Flag for Month 12 Months ago so I can pull up a sum against May 2019

[DebbieE]

I want to show total of a measure for the whole of May 2019 (this time Last year, for the month)

Ive got some DAX for Current Month

Current Month Flag =

IF (

YEAR ('Date'[Date].[Date] ) = YEAR ( TODAY () )

&& MONTH ( 'Date'[Date].[Date] ) = MONTH ( TODAY () ),

"Yes",

"No"

)

can I create another Flag For 12 months ago month?

[DebbieE · Accepted solution]

I have actually created it in M in power Query Editor

My date in date time. I took Year and Month from it and Merged. E.g. Jan 2020

Then created a New Column of Current Date 

DateTime.LocalNow() as datetime

then created a Year and Month from it 

Subtracted 1 from Year

Merged them Both together e.g. and merged again E.g. Jan 2019

And created a conditional column. If month Year  = Last years Month Year then 1 else 0

[amitchandak]

@DebbieE , you can use time intelligence with date calendar

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
//or use 
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[DebbieE]

I undertand what these measures are and I do have a date table connecting to my fact table.

However when I try and use this type of measure I get this

CALCULATE([Number of],SAMEPERIODLASTYEAR('Date'[Date].[Date]))

Without adding a filter this is fine. However once I add the filter for Current Month (This is all I want to see the Current Month and the Last years data against the current month, Last years dissapears

To me, this means I cant use this time of date time measure. I need a flag to set against all the days in the same month of May 2019 (And as Yet nothing seems to work)

[az38]

@DebbieE 

smth like

Current Month Flag 12 monthes ago =
IF (
YEAR ('Date'[Date].[Date] ) = YEAR ( TODAY () ) - 1
&& MONTH ( 'Date'[Date].[Date] ) = MONTH ( TODAY () ),
"Yes",
"No"
)

[DebbieE]

Current Month Flag 12 monthes ago =
IF (
YEAR ('Date'[Date].[Date] ) = YEAR ( TODAY () ) - 1
&& MONTH ( 'Date'[Date].[Date] ) = MONTH ( TODAY () ),
"Yes",
"No"
)

I have tried this but I just get a blank, even though I know there is no Yes data, even though there is definitely data for that Month