Previous Week and Previous Month DAX

[Anonymous]

Hi There,

I am fairly new to power BI and trying to figure this out.. I want to create a date table that will have a series how collumns that show a "Yes" or "No" value based on specfied date ranges e.g 1 day ago, 2 days ago, last week, last month etc. 

I have managed to get days working fine but I am struggling to get Last Month and Last week. Any idea on the formula I should use for these?

This is the formula I use to calculate yesterday:

Yesterday =
IF (
YEAR ( [Date] ) = YEAR ( TODAY () )
&& DAY ( [Date] ) = DAY ( TODAY () -1 ),
"Yes",
"No"
)

Any help would be much appricated!  Thanks

[v-yuta-msft · Accepted solution]

Hi wupdedu,

To achieve your requirement, create two calculate columns using DAX formula like pattern below:

Last Month = IF(MONTH([Date]) = MONTH(EDATE(TODAY(), -1)), "Yes", "No") 

Last Week = 
VAR Current_Week = CALCULATE(MAX([Week No]), FILTER(Table1, Table1[Date] = TODAY()))
RETURN
IF([Week No] = Current_Week, "Yes", "No")

 

Regards,

Jimmy Tao

[Anonymous]

Hi Jimmy,

Awesome thanks for that! The month one worked perfectly. The week one was still giving me current the current week.. I adjusted to this and seems to work giving me last week now:

Last Week = 
VAR Current_Week = CALCULATE(MAX([Week No]), FILTER(Table1, Table1[Date] = TODAY()))
RETURN
IF([Week No] +1 = Current_Week, "Yes", "No")

Thanks for your help!

[Greg_Deckler]

See other post