Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Previous Week and Previous Month DAX

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's avatar
    v-yuta-msft
    7 years ago

    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

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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!