Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Last month and Last Week DAX Formulas

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

 

 

  • Anonymous's avatar
    Anonymous
    7 years ago

    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"
    )

     

     

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    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"
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous,

         

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

         

        Regards,

        Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

  • kwatson1's avatar
    kwatson1
    Frequent Visitor
    3 years ago

    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"
    )