Forum Discussion
Date Crossfilter If Statement
- 8 years ago
The output visual was supposed to contain no date selections and no dates on the rows and columns. This is why the formula proved to be difficult. I was able to get the formula to work using a slightly different approach. I'll leave the formula here in case anyone in the future faces a similar problem.
Current Actual + Forecast Room Revenue:= VAR maxdate = MAX(Dates[Date]) VAR R3M = CALCULATE( [Actual + Forecast Room Revenue], ALL(Dates[Date]), DATESBETWEEN( Dates[Date], DATE(YEAR(maxdate), MONTH(maxdate)-2, DAY(maxdate)), maxdate ) ) VAR R12M = CALCULATE( [Actual + Forecast Room Revenue], ALL(Dates[Date]), DATESBETWEEN( Dates[Date], DATE(YEAR(maxdate), MONTH(maxdate)-11, DAY(maxdate)), maxdate ) ) RETURN SWITCH( SELECTEDVALUE(Periods[Period]), "YTD", IF( ISCROSSFILTERED(Dates[Date]), TOTALYTD([Actual + Forecast Room Revenue],Dates[Date]), TOTALYTD([Actual + Forecast Room Revenue], DATESBETWEEN(Dates[Date], MIN(Dates[Date]), TODAY()-1)) ), "MTD", IF( ISCROSSFILTERED(Dates[Date]), TOTALMTD([Actual + Forecast Room Revenue], Dates[Date]), TOTALMTD([Actual + Forecast Room Revenue], DATESBETWEEN(Dates[Date], MIN(Dates[Date]), TODAY()-1)) ), "QTD", IF( ISCROSSFILTERED(Dates[Date]), TOTALQTD([Actual + Forecast Room Revenue],Dates[Date]), TOTALQTD([Actual + Forecast Room Revenue], DATESBETWEEN(Dates[Date], MIN(Dates[Date]), TODAY()-1)) ), "R3M", R3M, "R12M", R12M, [Actual + Forecast Room Revenue] )
As you can see, I just did an if statement that will either give me the time intelligence function for all dates if the dates are filtered or the time intelligence function for yesterday if the dates are not filtered. I realize that this is not a typical use case, it was just a special request from my supervisor.Thank you all for all of your help, I couldn't have gotten to the solution without you all. Thank you!
What do want output visual to look like? What is on rows/columns? How are users selecting dates?
All the TOTALXXX functions need as a second parameter is a single date. That single date will be used to expand the date range as needed per the function called.
But if you put year, quarters, and months on rows of visual, all three TOTALXXX functions should compute correctly. No need to detect selection. Slicers could be used to filter visual to subset of data though.
So so a sample output would be helpful.
The output visual was supposed to contain no date selections and no dates on the rows and columns. This is why the formula proved to be difficult. I was able to get the formula to work using a slightly different approach. I'll leave the formula here in case anyone in the future faces a similar problem.
Current Actual + Forecast Room Revenue:= VAR maxdate = MAX(Dates[Date]) VAR R3M = CALCULATE( [Actual + Forecast Room Revenue], ALL(Dates[Date]), DATESBETWEEN( Dates[Date], DATE(YEAR(maxdate), MONTH(maxdate)-2, DAY(maxdate)), maxdate ) ) VAR R12M = CALCULATE( [Actual + Forecast Room Revenue], ALL(Dates[Date]), DATESBETWEEN( Dates[Date], DATE(YEAR(maxdate), MONTH(maxdate)-11, DAY(maxdate)), maxdate ) ) RETURN SWITCH( SELECTEDVALUE(Periods[Period]), "YTD", IF( ISCROSSFILTERED(Dates[Date]), TOTALYTD([Actual + Forecast Room Revenue],Dates[Date]), TOTALYTD([Actual + Forecast Room Revenue], DATESBETWEEN(Dates[Date], MIN(Dates[Date]), TODAY()-1)) ), "MTD", IF( ISCROSSFILTERED(Dates[Date]), TOTALMTD([Actual + Forecast Room Revenue], Dates[Date]), TOTALMTD([Actual + Forecast Room Revenue], DATESBETWEEN(Dates[Date], MIN(Dates[Date]), TODAY()-1)) ), "QTD", IF( ISCROSSFILTERED(Dates[Date]), TOTALQTD([Actual + Forecast Room Revenue],Dates[Date]), TOTALQTD([Actual + Forecast Room Revenue], DATESBETWEEN(Dates[Date], MIN(Dates[Date]), TODAY()-1)) ), "R3M", R3M, "R12M", R12M, [Actual + Forecast Room Revenue] )
As you can see, I just did an if statement that will either give me the time intelligence function for all dates if the dates are filtered or the time intelligence function for yesterday if the dates are not filtered. I realize that this is not a typical use case, it was just a special request from my supervisor.
Thank you all for all of your help, I couldn't have gotten to the solution without you all. Thank you!