SOLVED

# Divide the value from month granularity in one table to daily granularity in another table

Brass Contributor

# Divide the value from month granularity in one table to daily granularity in another table

I have a 'Target' table as below:

Accruals table as below:

Now i need to split the Target table value column to their corresponding point id and date in Accrual table. Unfortunately,we only have months in Target table instead of dates.
Our idea was to write a measure to find the no of days for months in target table. Then divide the units by  number of days to find units for corresponding points id in Accruals table. Below is the sample calculated column we tried. we are not sure whether this is correct or not.

But we couldn't achieve this using dax measure.Also, please suggest if you have different idea to achieve this

PFA file here Financial Management -ADB.pbix

9 Replies

# Re: Divide the value from month granularity in one table to daily granularity in another table

Finally, what you'd like to show, Target Unit Rate only or Target Units and Target Cost as well?

Will play with that some later, first thoughts

- you may add number of days in the month into Calendar table, like

``DaysInMonth = DAY( EOMONTH( 'Calendar'[Date], 0 ) )``

or better in Power Query

- current Target unit rate could be simplified to

``````Target unit rate =
ABS( DIVIDE( 'Accruals'[Target Cost] , 'Accruals'[Target units] ) )
``````

# Re: Divide the value from month granularity in one table to daily granularity in another table

The dax measure you gave for number of days is awesome Sir!

Also, thanks for simplifying the Target unit rate measure

We like to show 'Target Unit' and 'Target Cost' also as a dax measure

The Target Unit in target table comes under Target Type(Column) = 0,

The Target Cost in target table has Target Type = 1.

# Re: Divide the value from month granularity in one table to daily granularity in another table

Not sure I understood entire logic of the model. Here is variant.

Relationships are:

In Accrual table we add calculated column

``MonthID = YEAR( Accruals[Date] )*100 + MONTH( Accruals[Date] )``

to separate, for example, January 2023 and January 2024.

Since Accruals is filtered both by Points and Calendar we calculate number of days based on that table

``no of days in Accruals = COUNTROWS( 'Accruals' )``

Month Lookup we use as bridge table between Accruals and Target, for both 1:many. Thus we use CROSSFILTER in measures.

For Total Units

``````3-Target units Total = SUMX (
VALUES ( 'Accruals'[MonthID]  ),
CALCULATE (
SUM ( 'Target'[Value] ),
'Target'[TargetType] = 0,
CROSSFILTER ( 'Month Lookup'[Month ], 'Accruals'[Month], BOTH )
)
)
//////
3-Target units = DIVIDE (
[3-Target units Total],
[no of days in Accruals]
)``````

Visuals for testing are on Page 4

Didn't do Target Cost, too late today. Guess it shall be similar.

File is here TechComm - OneDrive (live.com) . Good luck!

# Re: Divide the value from month granularity in one table to daily granularity in another table

Many thanks for the lovely solution sir!

This solution has worked like a gem and as always you & your quick solution rocks!

We need your help in resolving the below issue:

we are trying to dynamically change between profile,direct and target units by referencing them in below measures that point to field parameters

Priority 1 units measures point to Parameter 4 field parameter table
Priority 2 units measures point to Parameter 5 table
Priority 3 units measures point to Parameter 6 table

When I drag them in below visual it shows blank for INSE-100:

But in data view there is a value for target but not showing in visual:

PFA file here Financial Management -ADB (1).pbix

best response confirmed by Excellove15 (Brass Contributor)
Solution

# Re: Divide the value from month granularity in one table to daily granularity in another table

If in code for the Priority 3 to change

it is shown

# Re: Divide the value from month granularity in one table to daily granularity in another table

Many thanks for this amazing quick solution Sir!

I tried the same in my file, but still i am getting blanks.

If you don't mind, can you please send me the file so that i can have a look into changes and update my file for other measures as well

Also, i have marked your solution as the best response!

# Re: Divide the value from month granularity in one table to daily granularity in another table

Hi, @Excellove15

File is here TechComm - OneDrive (live.com), see on Duplication of Page 4

# Re: Divide the value from month granularity in one table to daily granularity in another table

Many thanks Sir!

The report is working like a gem! Its a great learning experience Sir! I have closed this query

As always, please let me know if i can do anything from my end

# Re: Divide the value from month granularity in one table to daily granularity in another table

@Excellove15 , thank you, nothing. If I have any questions -will contact.

1 best response

Accepted Solutions
best response confirmed by Excellove15 (Brass Contributor)
Solution

# Re: Divide the value from month granularity in one table to daily granularity in another table

If in code for the Priority 3 to change

it is shown