Calculation involving multiple facts and trailing 12 months

[sbatra]

Hello community members,

I'm building a power bi report to include a few key metrics for a business. One of the metric is Gross Margin Amount (GMA). The report has a slicer, through which the user can select an year and a month (Dimdate table in the model). Here is the image of the model. 

I have a table fact billed revenue with column billed amount and bill date (amoung other attributes). I have another table fact unbilled revenue with columns unbilled amount and visit date and another table fact cost with columns total cost and transaction date (among other columns).  

Requirement: - 

I need to create a measure to calculate gross margin amount (GMA), for the trailing 12 months based upon the year, month selection in the slider, by adding unbilled revenue (fact unbilled revenue), billed revenue (fact billed revenue) and then subtract total cost (fact cost) to get to the final amount. 

Here is the DAX formula that I have created. 

Trailing 13 Months GMA v1 =  

VAR SelectedDate = COALESCE(SELECTEDVALUE('DimDate'[Date]), MAX('DimDate'[Date])) 

RETURN 

CALCULATE( 

    SUM('Fact Billed Revenue'[BilledAmount]) + 

    SUM('Fact UnBilled Revenue'[UnBilledAmount]) - 

    SUM('Fact Cost'[TotalPayrollCost]), 

    DATESINPERIOD('DimDate'[Date], SelectedDate, -12, MONTH), 

    ALLEXCEPT('DimDate', 'DimDate'[Date]) -- Keep only the date context 

) 

I then put the measure “Trailing 13 Months GMA v1” in the table visual and then pulled in the year and month from the date dimension. 

Expectation: Correct GMA amount for the trailing 12 months i.e. June 2023 to May 2024 

However, as you can see, something is not right here. Not only do I see the same GMA amount for each trailing month (suggesting the slicing operation is not working at all), I also see the unexpected date range on the x-axis.  

As I’m analyzing this issue, I would appreciate any pointers on  

• what could be wrong here?  mostly like the DAX measure needs some changes or something else is going on here. 

• What tool or method I can use for debugging, so that I can see intermediate results, step through calculations and filters interplay. 

Thank you for your inputs! 

[Ashish_Mathur]

Hi,

Try this measure pattern

=calculate(SUM('Fact Billed Revenue'[BilledAmount]) + SUM('Fact UnBilled Revenue'[UnBilledAmount]) -SUM('Fact Cost'[TotalPayrollCost])

,datesbetween(calendar[date],edate(min(calendar[date]),-12),max(calendar[date])))

Hope this helps.

[sbatra]

Hi @Ashish_Mathur, Thanks for your response.

The suggestion didn't work. A peculier situation here (as it appears to me atleast) is we have a situation where we need to remove the filter context coming in from the slider (date dim) and also retain it when with do GMA calculation in the measure, so that we can slice the three fact tables when we put date dim in the x-axis along with the GMA measure. Any further thoughts?

[Ashish_Mathur]

You are welcoe.  No further suggestions unless i see the file.  Show the problem and the expected result very clearly.

[danextian]

Please post a sanitized copy your pbix so we will know what's going on inside. Or we will be simply trying to shoot a target without properly  aiming at it.

[danextian]

Hi @sbatra 

Remove this line: ALLEXCEPT('DimDate', 'DimDate'[Date]) -- Keep only the date context
Mark a date table as a date table. Doing so automatically applies REMOVEFILTERS on the table when using time intelligence functions.

If you don't mark a date table as one, make sure to use the correct filter modifier in the measure.

Sales L12M = 
CALCULATE (
    [Sales],
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH ),
    REMOVEFILTERS ( 'Date' )
)

[sbatra]

@danextian Thanks for the response.

I seem to understand what you mean here. I used the measure based approach (since the other approach is a bigger impact on the existing report). Unfortunately, this didn't work. Would you have any further thoughts?

Revised Measure (FYI, there is no change in the calcuation if I keep or remove REMOVEFILTERS statement)

Trailing 13 Months GMA =

CALCULATE(

    SUM('Fact Billed Revenue'[BilledAmount]) + SUM('Fact UnBilled Revenue'[UnBilledAmount]) - SUM('Fact Cost'[TotalPayrollCost]),

    DATESINPERIOD(

        DimDate[Date],

        Max('DimDate'[Date]),

        -12,

        MONTH

    ),

       REMOVEFILTERS ( 'DimDate' )

)