Forum Discussion
Solution SpecialistYTD last year DAX
It should work fine in Power BI.
You didn't have any dates in your Data table beyond 3 Jan 2017 did you?
Here is a sample PBIX file with your data posted above where the measure is working:
I made a small change to the YTD measure so that it is not displayed after the max date in Data table, but the YTD LY measure is the same as I posted.
Have a play with that - there must be some difference in your model if it is not working.
Owen
Solution SpecialistHi OwenAuger,
could you please help me with construction of formula to calculate Sales MTD LM (Last month)?
So I have standard measure for Sales MTD
Sales MTD = TOTALMTD(SUM(Data[Amount]);'Date'[Date])
And I would like to have measure to show me Sales MTD for last month - so when now is 17.1.2017, I have sales from 1.1.2017 to 17.1.2017. And then I would like to have Sales MTD LM from 1.12.2016 to 17.12.2016.
Is it possible, when the year in filter is set to 2017?
Thanks.
Pavel
Super UserHi PavelR
I would suggest a similar pattern to the YTD LY measure.
You can use DATEADD instead of SAMEPERIODLASTYEAR in this case.
INTERSECT(...) returns the selected date range limited by the latest date in the Data table, then DATEADD shifts it one month earlier.
Sales MTD Last Month (limited by last date in Data table) =
VAR DataMaxDate =
CALCULATE ( MAX ( Data[Date] ); ALL ( Data ) )
RETURN
CALCULATE (
[Sales MTD];
DATEADD (
INTERSECT (
VALUES ( 'Date'[Date] );
DATESBETWEEN ( 'Date'[Date]; BLANK (); DataMaxDate )
);
-1;
MONTH
)
)Cheers,
Owen :)
