Multiple If Statements DAX

[Brettacus]

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 quarters. 

That part is simple as the below formual works fine.

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

But when we get to the 1st quarter of the year it obviously won't work, so I came up with this formula. (The [Today] is just a column I made so i could change the date to a date in the first quarter to test the logic. 

First Quarter =
var _to=[today]
return IF(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)))

Finally I had to come up with a third formula to simulate the second quarter so it would show first two plus 4th.

Second Quarter =
var _to=[today]
return IF(QUARTER(_to)=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)))

All three of these logics work independently and I have even successfully combined the first logic with the second and It would work as expected when I change the date. Where I am running into issues is if I try to add in the thrid logic for the second quarter. When added with the other 2 it just shows the two quarters from the current year, but not the fourth. 

I assume that order of the if statments matter and something is canceling the other out.

Can anyone help me come up with a code that will do what I am trying to do? Show current quarter along with previous two completed quarters, accounting for when you will need to show quarters from two seperate years. (Q1 & @Q2)

[johnyip · Accepted solution]

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

)

[Brettacus]

This works perfect! Thanks. 

I actually came here to post that I figure it out by using the below. Basically I added if the quarter is 1 or 2 then show Q4 from last year and that solved my problem, but your solution is much cleaner. I was going to look into Switch today if I couldn't get an answer. 

Flag7 =
var _to='Table'[Today]
return IF(AND(YEAR([Date])=YEAR(_to),OR(QUARTER(_to)=QUARTER([Date]),OR(QUARTER(_to)-1=QUARTER([Date]),QUARTER(_to)-2=QUARTER([Date])))),1,
IF(OR(QUARTER(_to)=2,QUARTER(_to)=1),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,
IF(QUARTER(_to)=1,IF(AND(YEAR(_to)-1=YEAR([DATE]),QUARTER([Date])=3),1,IF(AND(QUARTER(_to)=QUARTER([Date]),YEAR(_to)=YEAR([Date])),1,0)))))))

[Ashish_Mathur]

Hi,

I am not sure how much i can help but i would like to try.  In the Table/matrix visual, do you want to see Quarters or months in the headers?  Also, share data in a format that can be pasted in an MS Excel file.

[Brettacus]

I believe I figured out why the nested if will not work, but I can't figure out how to solve for it. 

I believe it is because Quarter([Date])=4 is used twice and can only be true once, so depending what order i put it in one of the scenarios will be missing that quarter because the the value gets changed from true to false depending on the order I place it in. 

Hoping this additional context can help. 

[ToddChitt]

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]

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]

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]

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. 

[gmsamborn]

Hi @Brettacus 

Try these:

DimDate.m

Date Dimension Setup using M script.docx