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)

)

     

     

     

     

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.