Calculated measure with hierarchical data

[anettbaksa]

Hello everybody, 

I have an issue with Previous year to date sales DAX calculation. 

Prev Yr YTD Sales/1M = CALCULATE(SUM('SALES VALUE'[VALUES]),DATESBETWEEN('SALES VALUE'[PERIOD],FIRSTDATE('SALES VALUE'[PERIOD]),[selected date]-365))

In the picture, we can see the "selected date" column. This date column shows the last data delivery time and the Previous YTD sales calculation must adjust to this date. For example the calculated measure at "Instant noodles" subcategory has to show just 2020. January - this will be the previous YTD period. "Cook up" soup has to show 2020 January and February (However the date shows always the first day of the month these are whole month data, the delivery happens once a month). 

In Subcategory level my calculation works correctly but in Category level it's not working. This is logical because my calculation use "MAX" DAX expression and at Category level the max will be 2021.02.01. 

I need some tips how can I SUM the Subcategory level to Category level correctly. 

[v-deddai1-msft · Accepted solution]

Hi @anettbaksa ,

Would you please try to use the following measure:

Prev Yr YTD Sales/1M =
SUMX (
    SUMMARIZE ( 'SALES VALUE', 'SALES VALUE'[CATEGORY], 'SALES VALUE'[SUNCATEGORY] ),
    CALCULATE (
        SUM ( 'SALES VALUE'[VALUES] ),
        DATESBETWEEN (
            'SALES VALUE'[PERIOD],
            FIRSTDATE ( 'SALES VALUE'[PERIOD] ),
            [selected date] - 365
        )
    )
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[daxer-almighty]

@anettbaksa 

Before you start doing time calculations (the way you do), please read this first to see how to do it correctly: Standard time-related calculations – DAX Patterns

Also, please read this to see what it means to have a correct model in PBI.