Forum Discussion

MicheleGenerali's avatar
MicheleGenerali
Frequent Visitor
3 years ago

Calculate MoM with specific Date

Hi all, 
i have table query with 4 columns : DATE, SALES, SALES 22 and SALES 23. 
My date column has date from 01-jan-2022 to today, the SALES column have the value and the SALES 22 and 23 columns are conditional columns with values only for the specified year.  The problem is that my sales values are cumulative, so for february I have jan+feb, for march i have jan+ feb + march....
I'm tryng to find the MoM for each year so i can compare Month sales from Jan23 to sales of Jan22 and so on, and i want to have an Histogram like this : 

I have 2 measures to calculate MoM for each year but having only one date column when i try to compare them, my jan 2023 sales values are negative.

SALES 23 MoM 2 =
    VAR __PREV_MONTH =
        CALCULATE(
            SUM('Table'[SALES 23]),
            DATEADD('Table'[DATE].[Date], -1, MONTH)
        )
    RETURN
        SUM('Table'[SALES 23]) - __PREV_MONTH

 

I also tried to work with 2 different table, one for 2022 and one for 2023, and link them to a calendar table, and in this case the measures work but i cant compare MoM in my histogram because of the dates, as i have an X-axis starting at jan-2022 and ending in dec-2023.
Can someone help me ? 
Thank u in advance

Need Help

1 Reply