Forum Discussion

Excellove15's avatar
Excellove15
Iron Contributor
Feb 26, 2024

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

Hi SergeiBaklan ,

 

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

Please help us write a dax measure to achieve this

 

PFA file here Financial Management -ADB.pbix

 

Thanks in advance!

 

  • Excellove15 

    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] ) )
    
    • Excellove15's avatar
      Excellove15
      Iron Contributor

      Hi SergeiBaklan 

       

      Thanks for your quick response!

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

      Also, thanks for simplifying the Target unit rate measure:smile:

       

      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.  

       

      Thanks in advance!

       

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP

        Excellove15 

        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!

Resources