Forum Discussion

vega's avatar
vega
Icon for Resolver III rankResolver III
8 years ago
Solved

Date Crossfilter If Statement

Hello,  I have the following formula and it is throwing an error and I am not sure why VAR test = IF( ISCROSSFILTERED(Dates[Date]), Dates[Date], DATESBETWEEN(Dates[Date], MIN(Dates[Dat...
Need Help
  • vega's avatar
    vega
    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!

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Hi vega

 

Try this. Could you share the complete MEASURE you are using

 

 

VAR test =
IF(
	ISCROSSFILTERED(Dates[Date]), 
	ALL(Dates[Date]), 
	DATESBETWEEN(Dates[Date], MIN(Dates[Date]), TODAY()-1)
)
vega's avatar
vega
Icon for Resolver III rankResolver III
8 years ago

Sure, I didn't want to complicate it with a lot of code.

 

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
	)
)
VAR test =
IF(
	ISCROSSFILTERED(Dates[Date]), 
	Dates[Date], 
	DATESBETWEEN(Dates[Date], MIN(Dates[Date]), TODAY()-1)
)
RETURN

SWITCH(
	SELECTEDVALUE(Periods[Period]),
	"YTD", TOTALYTD([Actual + Forecast Room Revenue],test),
	"MTD", TOTALMTD([Actual + Forecast Room Revenue], test),
	"QTD", TOTALQTD([Actual + Forecast Room Revenue], test),
	"R3M", R3M,
	"R12M", R12M,
	[Actual + Forecast Room Revenue]
)
  • mattbrice's avatar
    mattbrice
    Icon for Solution Sage rankSolution Sage
    8 years ago

    First, the error you got is because Dax can't tell which row(s) of Dates[Date] you are referring to. You need to do like suggested:  ALL (Dates[Date] ) or VALUES ( Dates[Date] ).  But even more importantly ...

     

    If you replace 'test' with 'Dates[Date]' , the 3 switch lines 'YTD', 'QTD' and 'MTD' should yield the same results.  The functions TOTALYTD, TOTALQTD and TOTALMTD second paramter is only a date column refernce - It doesn't use the column you computed otherwise.  Put another way, I don't believe the variable 'test' is impacting the calculation like you think it is.

     

    Dax converts your call to the TOTALYTD function to this equivalent in Dax:

     

     

    TOTALYTD Measure = CALCULATE ( [Actual + Forecast Room Revenue], 
    CALCULATETABLE ( FILTER ( ALL ( 'Dates'[Date] ), 
    AND ( 'Dates'[Date] <= MAX ( 'Dates'[Date] ), 
          YEAR ( 'Dates'[Date] ) = YEAR ( MAX ( 'Dates'[Date] ) ) ) ) )

     

    TOTALQTD & TOTALMTD Dax are similar.

     

     

    so with the ALL( ) function call, the column values in your 'test' var are being overwritten. (or more precisely, your 'test' column filters are being blocked & replaced by this setfilter argument to CALCULATE).

     

    It is hard to say what the measure code should be without fully understanding what you are trying to do.  I'm not sure i understand why you want to detect if 'Dates' is being crossfiltered(?) But hope this helps.  

     

    • vega's avatar
      vega
      Icon for Resolver III rankResolver III
      8 years ago

      I have a question about the code below:

      TOTALYTD Measure = CALCULATE ( [Actual + Forecast Room Revenue], 
CALCULATETABLE ( FILTER ( ALL ( 'Dates'[Date] ), 
AND ( 'Dates'[Date] <= MAX ( 'Dates'[Date] ), 
      YEAR ( 'Dates'[Date] ) = YEAR ( MAX ( 'Dates'[Date] ) ) ) ) )

      The ALL function there is wiping out the filters from the test variable but it is replacing the filter with a date filter that will contain the year to date of the test variable. This is exactly the expected behavior that I want. This is what I am trying to accomplish here:

      My data contains forecast data that can go into 2019. When the user wants a YTD, but they do not specify the dates (is not crossfiltered), then I want to retrieve the YTD from yesterday, hence TODAY()-1. If the user does specify a date, then just calculate the YTD normally, hence just return Dates. If I just use the Date column, then if the user does not specify a date, we get YTD of the last months of data in 2019.

      I tried the following:

      VAR testdate =
IF(
	ISCROSSFILTERED(Dates[Date]), 
	VALUES(Dates[Date]),
	DATESBETWEEN(Dates[Date], MIN(Dates[Date]), TODAY()-1)
)

      But when I use that this I get "The first argument in DATESYTD must specify a column". I'm sure this is because I am feeding testdate to TOTALYTD and because testdate is not a column, I am getting this error. I guess my question is there any way to get DAX to understand that testdate is a column, because technically it is a one column table?

       

      I appreciate the help you guys have given me so far. Thanks!

      • mattbrice's avatar
        mattbrice
        Icon for Solution Sage rankSolution Sage
        8 years ago

        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.