Forum Discussion

Brettacus's avatar
Brettacus
Frequent Visitor
3 years ago
Solved

Multiple If Statements DAX

Hello,   I have been using Power Bi for a few months and I am just starting to get into Dax. Currently I have a report and I only want it to show data for the current quarter plus the previous two ...
Need Help
  • johnyip's avatar
    johnyip
    3 years ago

    Brettacus , please try this measure.

     

     

     

    Last 3 =
var _to=TODAY()
return 
SWITCH(QUARTER(_to),
1,
IF(AND(YEAR(_to)-1=YEAR([DATE]),OR(QUARTER([Date])=4,QUARTER([Date])=3)),1,IF(AND(QUARTER(_to)=QUARTER([Date]),YEAR(_to)=YEAR([Date])),1,0)),

2,
IF(AND(YEAR(_to)-1=YEAR([DATE]),QUARTER([Date])=4),1,IF(AND(OR(QUARTER(_to)=QUARTER([Date]),QUARTER(_to)-1=QUARTER([Date])),YEAR(_to)=YEAR([Date])),1,0)),

IF(AND(YEAR([Date])=YEAR(_to),OR(QUARTER(_to)=QUARTER([Date]),OR(QUARTER(_to)-1=QUARTER([Date]),QUARTER(_to)-2=QUARTER([Date])))),1,0)

)

     

     

     

     

ToddChitt's avatar
ToddChitt
Super User
3 years ago

To be honest I didn't read much of this post, expecially when you started telling us how you are filtering on the last three quarters using DAX.

 

Do you know about Slicers?

Do you know how to add a Date Slicer to a page?

Do you know how to make that Date Slicer a 'relative date slicer'? You can set it for various date slices and it auto-magically adjusts around the current (relative) date. Hence the name: Relative Date Slicer.

 

Ditch the DAX

Brettacus's avatar
Brettacus
Frequent Visitor
3 years ago

I am not really sure if you were tying to be helpful, but I seriously doubt it. 

 

Did you know that relative date does not show the the full complete quarters if you filter by X months? (Hence the name relative, as it is relative to the current date.)

Did you know that some companies hat want dashboards built want things don't require the user to filter to a specific date range?

 

If I wanted to just add a slicer to the page then I would just make a slicer on year-quarter and have the user select the three they wanted to look at, but that is not the ask. 

 

Thanks for the effort. 

  • gmsamborn's avatar
    gmsamborn
    Super User
    3 years ago

    Hi Brettacus 

    I'm not sure what your date dimension looks like but have you looked into Offset columns?

    This example has Offset columns for Year, Quarter, Month, Day, and Week (needs work!)

    DimDate__M_wInstructions.zip

    • Brettacus's avatar
      Brettacus
      Frequent Visitor
      3 years ago

      Thanks for the answer. I actually can't open that .zip file because my work blocks it, but I might understand the premis. You can correct me if I am wrong. 

       

      I initally tried something like this where I would look at the year and assign it a number based on where it fell. Current year =0, previous -1, year ahead +1. This worked great to get last full year plus current, but i couldnt find a reliable way to assign a number to Year & Quarter together since the quarter repeats. It worked for Year because the year will be unique year over year.