How to find difference between two level between the columns in DAX

Question

I am trying to find the difference between Quarters in two different years in Power BI DAX. Below is the result i am expecting.

I tried this calculation but dint get the expected result. Please help

Diff = 
CALCULATE (
   SUM(Sample_Data[# Phone Inbound Forecast]),
    FILTER (
        ALL (Sample_Data[Submit Month] ),
        Sample_Data[Submit Month]= "FY20BD"
    )
)
    - CALCULATE (
        SUM(Sample_Data[# Phone Inbound Forecast]),
        FILTER (
            ALL (Sample_Data[Submit Month]),
           Sample_Data[Submit Month] = "Dec-19"
        )
    )

Ok, I've got a bunch of questions 1. Can you provide dataset in an usable format? (maybe a csv pasted as text in a code block). 2. Do you have control on the input dataset? can you edit it by adding columns? 3. You talk about difference between quarter, but the "Fiscal Calendar Quarter" is not even used in the formula, instead you are using the "Submit date", can it be considered a sort of "Source Type"? (like Actual | Forecast) 4. Do you have a calendar table in your model? — Giovanni Luisotto

CR7SMS CR7SMS · Accepted Answer · 2020-01-31T16:48:55

In cases like this dealing with a measure can be a little confusing/complicated. Assuming your data is already grouped up at a Business, Submit Month and quarter level, you could create a column using a calculation like this:

Diff = 
    VAR Business = Sample_Data[Business]
    VAR FiscalQuarter = Sample_Data[Fiscal Calendar Quarter]
RETURN 
    IF(Sample_Data[Submit Month]="FY20BG", 
            Sample_Data[Volume]-
                CALCULATE(SUM(Sample_Data[Volume]),
                ALL(Sample_Data),
                Sample_Data[Business]=Business,
                Sample_Data[Fiscal Calendar Quarter]=FiscalQuarter,
        Sample_Data[Submit Month]="19-Dec"),
        0)

The idea is to pass the values of Business and quarter and find the corresponding volume for "19-Dec" only when the month is "FY20BG".

Once the column is created, you can simply sum it up in the values section to get the result you are looking for. Hope this helps.

How to find difference between two level between the columns in DAX

1 Answers