Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to build a caluclation, which shows me MTD till last year same period.
I want to see the numbers deom Jan 2019 till August 2019?
I wrote multiple calculation, but not yeilding the desierd result.
Any helps is appreciated!
Solved! Go to Solution.
@JCK2 , With a date table and time intelligence
Ventas MTD: CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
Last year MTD Sales ? CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales - CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Mes detrás de Ventas: CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Año detrás de Ventas: CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Last MTD Sales - CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
Ventas del último mes: CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
Last MTD (completar) Ventas : CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
valor del mes anterior: CALCULATE(sum(''Table''[valor total de horas]),previousmonth('Date'[Date]))
diff - [Ventas MTD]-[las latest ventas MTD]
diff % a divide([MTD Sales]-[last MTD Sales],[last MTD Sales])
The same year last year value of the same month : CALCULATE(sum(''Table''[total value of hours]), previousmonth(dateadd('Date'[Date],-11,MONTH)))
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
To get the best out of the time intelligence function. Make sure that you have a date calendar and that it has been marked as the date in the model view. Also, join her with the date column of your events. Recommended:
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
We appreciate your congratulations.
Hello @JCK2
Try something like this:
Revenue MTD PY =
VAR __today = TODAY()
VAR __lastYear = EDATE ( __today, -12 )
RETURN
CALCULATE (
[Revenue MTD],
SAMEPERIODLASTYEAR ( 'Dim Calendar'[Date] ),
'Dim Calendar'[Date] <= __lastYear
)
O
MTD LY = CALCULATE(TOTALMTD(SUM('Calendar'[Amount]),'Calendar'[Date]),SAMEPERIODLASTYEAR('Calendar'[Date]))
If it doesn't help, you might consider providing your fictitious pbix that would be helpful for us to investigate further.
Hello @JCK2
Try something like this:
Revenue MTD PY =
VAR __today = TODAY()
VAR __lastYear = EDATE ( __today, -12 )
RETURN
CALCULATE (
[Revenue MTD],
SAMEPERIODLASTYEAR ( 'Dim Calendar'[Date] ),
'Dim Calendar'[Date] <= __lastYear
)
O
MTD LY = CALCULATE(TOTALMTD(SUM('Calendar'[Amount]),'Calendar'[Date]),SAMEPERIODLASTYEAR('Calendar'[Date]))
If it doesn't help, you might consider providing your fictitious pbix that would be helpful for us to investigate further.
@JCK2 , With a date table and time intelligence
Ventas MTD: CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
Last year MTD Sales ? CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales - CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Mes detrás de Ventas: CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Año detrás de Ventas: CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Last MTD Sales - CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
Ventas del último mes: CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
Last MTD (completar) Ventas : CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
valor del mes anterior: CALCULATE(sum(''Table''[valor total de horas]),previousmonth('Date'[Date]))
diff - [Ventas MTD]-[las latest ventas MTD]
diff % a divide([MTD Sales]-[last MTD Sales],[last MTD Sales])
The same year last year value of the same month : CALCULATE(sum(''Table''[total value of hours]), previousmonth(dateadd('Date'[Date],-11,MONTH)))
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
To get the best out of the time intelligence function. Make sure that you have a date calendar and that it has been marked as the date in the model view. Also, join her with the date column of your events. Recommended:
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
We appreciate your congratulations.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |