Last month and Last Week DAX Formulas

[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

[kwatson1]

@Greg_Deckler , @Mich I was unable to get this to work as written above, but was able to adjust the parenthises in the formula to get it to work:

B_MEAS_SchedLastWeek =

IF (

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

&& WEEKNUM ( [StartDate].[Date] ) = WEEKNUM ( TODAY() ) -1,

"Yes",

"No"

)

[Greg_Deckler]

Perhaps:

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

Last Week =
IF (
YEAR ( [Date] ) = YEAR ( TODAY () )
&& WEEKNUM ( [Date] ) = WEEKNUM ( TODAY ()) -1 ),
"Yes",
"No"
)

[Derwilf]

Hi, how will you obtain the first date and last dates of last 2 weeks for the first , second and third weeks of January?

[Anonymous]

Hi Greg, 

Thank you for your reply. I tried your suggestion but I get the following error

The syntax for ',' is incorrect. (DAX(IF (YEAR ( [Date] ) = YEAR ( TODAY () )&& MONTH( [Date] ) = MONTH( TODAY ()) -1 ),"Yes","No"))).

Thanks

[v-shex-msft]

Hi @Anonymous,

It seems like your system not support ',' as separator, you can try to use ';' to instead ','.

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Hi Xioxin,

Thanks for the reply. I tried that and still get the same error.

The "," is working when I use it in other formulas. Example this works:

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

[Mich_J]

Helo, Correct formula below -there was an extra ) next to today

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

Last Week =
IF (
YEAR ( [Date] ) = YEAR ( TODAY () )
&& WEEKNUM ( [Date] ) = WEEKNUM ( TODAY () -1 ),
"Yes",
"No"
)