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.
I have sales data from 1st of Jan 2016 until 4th of June 2017.
I would like compare the total sales of first 4 days in June 2017 to same period in 2016, but I always get as comparison the whole month of June from 2016. It should also run as MTD comparison, when I update more sales data for June 2017.
Is there a way to compare only those 4 days from each year?
Solved! Go to Solution.
You can just create a MTD LY measure and use SAMEPERIODLASTYEAR() in CALCULATE().
MTD LY = CALCULATE(TOTALMTD(SUM('Calendar'[Amount]),'Calendar'[Date]),SAMEPERIODLASTYEAR('Calendar'[Date]))
Regards,
You can just create a MTD LY measure and use SAMEPERIODLASTYEAR() in CALCULATE().
MTD LY = CALCULATE(TOTALMTD(SUM('Calendar'[Amount]),'Calendar'[Date]),SAMEPERIODLASTYEAR('Calendar'[Date]))
Regards,
I used DX formula as
I´m also trying to get the same anwer using the formula, but no luck so far.
MTD LY = CALCULATE(TOTALMTD(SUM('Calendar'[Amount]),'Calendar'[Date]),SAMEPERIODLASTYEAR('Calendar'[Date]))
VENTAS MTD = CALCULATE(TOTALMTD(SUM(RUADETALLE[CANTIDAD]);Calendario[Date];SAMEPERIODLASTYEAR(Calendario[Date])))
I get empty cells.
Hope someone can help me.
Thank you,
Hi,
Below formula worked for me.
Output is Revenue for same period last year calculated until same date of current period.
Revenue SPLY MTD = TOTALMTD(SUM('FactTable'[Revenue]), DATEADD(FILTER(DATESMTD('Calendar'[DateID]), 'Calendar'[DateID]<TODAY()), -1,YEAR))
Hi, How does this formula work when the date column has multiple time values in it? How can we exclude duplicates caused by the time factor?
The formula works against the calendar table which won't have duplicate dates in the date column.
This is not working when I used it, how to calculate at prodcut level then sku level ..then MTD then MTD last year( MTD = 1 to 12 July for current and for last year).. any suggestion..
It's working fine when i am using Datesbetween using static dates but i need to convert it dynamically
@VijayReddy wrote:Hi,
Below formula worked for me.
Output is Revenue for same period last year calculated until same date of current period.
Revenue SPLY MTD = TOTALMTD(SUM('FactTable'[Revenue]), DATEADD(FILTER(DATESMTD('Calendar'[DateID]), 'Calendar'[DateID]<TODAY()), -1,YEAR))
This article will walk you through how to fix the problem you are having.
https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/
It shows how to use filtering on a special column you add to your date table to stop calculations from going past the point where you have current data.
PY MTD Limited = CALCULATE( [MTD amount], CALCULATETABLE( SAMEPERIODLASTYEAR(Dates[Date]), Dates[Is Past] = TRUE ) )
I used DX formula as
I also used this code and it is not returning any values for last year MTD. I am sure we do have data for last year. However, no errors in the formula.
On your date table you must create the following measures:
BeginCurrentYear = DATE(YEAR(TODAY());1;1)
BeginPreviousYear = DATE(YEAR(TODAY())-1;1;1)
SameDay = TODAY()
SameDayPreviousYear = DATE(YEAR(dCalendar[SameDay])-1;MONTH(dCalendar[SameDay]);DAY(dCalendar[SameDay]))
and then, this calculated column
CurrentPreviousYearToDate = IF(AND(dCalendar[BeginCurrentYear]<=dCalendar[Date];dCalendar[Date]<=dCalendar[SameDay]);"1";IF(AND(dCalendar[BeginPreviousYear]<=dCalendar[Date];dCalendar[Date]<=dCalendar[SameDayPreviousYear]);"0"))
so your measures for MTD are
Sales MTD = CALCULATE(TOTALMTD([Sales];DATESMTD(dCalendar[Date]));dCalendar[Month No]=MONTH(TODAY());dCalendar[Year]=YEAR(TODAY()))
and
Sales PYMTD = CALCULATE([Sales];DATEADD(DATESMTD(dCalendar[Date]);-1;YEAR);dCalendar[Month No]=MONTH(TODAY());dCalendar[Year]=YEAR(TODAY())-1;dCalendar[CurrentPreviousYearToDate]="0")
This worked perfectly, it is just what i was looking for!
Thank you!
Thank you for the code about MTD Comparison. It is very helpfull.
I am testing your code, and evrything looks fine, but i am not getting the sales for PYMTD.
Thanks
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 |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |