Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a calculated column to get the total amount in a month from last year from a transactional table, to be compared with the MTD figure in the current year in a visual.
The formula applied is
Amount MTD Previous Year. = CALCULATE(SUM('Transation'[Amount]),DATEADD(DATESMTD(DateCalendar[Date]),-12,MONTH))
And I have another table consists of calendar dates from several years period.
I use this calendar date table as an X-axis and filtering month and year.
The screen shot below is the visual I mentioned.
The result given from the previous year MTD from Jan-Nov is correct.
However, the Dec figure shows incorrect result. It's showing the total amount of Jan-Nov.
I need help to show me what is wrong with the formula & how to fix this, so I can get the correct figure to show MTD of each month from the previous year of the year that is selected as filter.
Thanks.
Solved! Go to Solution.
Hi,
Try this measure (not a calculated column formula)
=CALCULATE(SUM('Transation'[Amount]),SAMEPERIODLASTYEAR(DateCalendar[Date]))
Does this work?
Hi,
Try this measure (not a calculated column formula)
=CALCULATE(SUM('Transation'[Amount]),SAMEPERIODLASTYEAR(DateCalendar[Date]))
Does this work?
Hi @Ashish_Mathur,
I got the problem. It's because of the data.
Sorry to bother you.
Thanks anyway. I will keep in mind your suggestion next time.
@RMV,
To help close this thread, you may accept solution. Your contribution is highly appreciated.
You are welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |