Help in DAX Building

[apatwal]

Hi,

I have below requirement to create DAX.

I need to take difference of margin for 2022 year and 2021 year on weekly basis and plot this in column chart that should show only 2022 year data month wise in normal as well as in a cumulative way.

For example : For week of Jan 3, 2022, we need to calculate the week’s total margin and then subtract the margin from week of Jan 4, 2021 and plot this difference month wise on column chart and after that need to take cumulative of these values also.

For sample records, please find attached PBI file.

PBI Sample File

Thanks..

[v-jingzhang · Accepted solution]

Hi @apatwal 

You can add a Year column to your table

Year = YEAR('Sample Data'[Week Start Date])

Then create the following measures:

Margin This Year = SUM('Sample Data'[Margin $])

Margin Previous Year = 
VAR _thisYear = MAX('Sample Data'[Year])
VAR _thisWeek = MAX('Sample Data'[Week No])
RETURN
CALCULATE(SUM('Sample Data'[Margin $]),ALL('Sample Data'),'Sample Data'[Year]=_thisYear-1,'Sample Data'[Week No]=_thisWeek)

Margin Diff = [Margin Previous Year] - 'Sample Data'[Margin This Year]

Cumulative Diff =
SUMX (
    FILTER (
        CALCULATETABLE (
            VALUES ( 'Sample Data'[Week Start Date] ),
            ALLEXCEPT ( 'Sample Data', 'Sample Data'[Year] )
        ),
        'Sample Data'[Week Start Date] <= MAX ( 'Sample Data'[Week Start Date] )
    ),
    [Margin Diff]
)

Drag Week Start Date and measures into a visual. Put Year column into the visual as a filter field and set its value is 2022. 

You will get below result. 

* I don't make it month wise in the column chart. In the following example, the week start date of week 5 in 2022 is in January but the same day of 2021 is in February. When they are in different months, the measure may return a wrong result when the axis is month wise. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@apatwal , To get week year behind measure try like example

week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

For normal year

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

This should give diff any period vs any period based in grouping 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs