How to conditionally filter a measure based on the dimension in the chart

[meddojeddo]

I have a table chart which is sliced by year as the dimension. I want to create a measure which displays sales for the year prior to whichever year is in that row. Except for the current year, I want it to be YTD. However the data is stored at the Period level, which is in the format 202001,202001,202003, etc. What I am looking to build is this basically:

I have tried using an if statement with calculate, but I get errors either "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed." or "Cannot compare data type text to numeric." What would be the correct way to create this measure?

[PaulDBrown · Accepted solution]

@meddojeddo 

Oh. So what fields are in your 'date dimension' then? Or is 'date dimension' your fact table?

Can you post an image of your dimension table, fact table and model?

Otherwise you might try:

Combined Measure = 
VAR PreviousYearSales = CALCULATE([Sales],
FILTER(ALL(date dimension[Year]),
date dimension[Year] = MAX(date dimension[Year]) -1))
VAR PYYearMonth = (YEAR(TODAY())-1)* 100 + MONTH(TODAY())
VAR _PYTDSales = CALCULATE([Sales],
FILTER(ALL(date dimension),
date dimension [year] = YEAR(TODAY())-1
&&
date dimension [YearMonth] <= _PYYearMonth))
RETURN
IF(SELECTEDVALUE(date dimension [year]) = YEAR(TODAY()), _PYTDSales, PreviousYearSales)

[amitchandak]

@meddojeddo , you can use rank/month year table with the year is such a case. Check the link of week and MTD blog at the end

You can create a date and use time intelligence

Date = date(left([month],4), right([month],2),1)

This with time intelligence and date table. Example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

This will even work with a separate month year with year

//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

refer approach not using time intelligence 

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[meddojeddo]

@amitchandak Will this work even if my date dimension only displays Period(202001, 202002, 202003, etc.) and not day?

[PaulDBrown]

@meddojeddo 

Does your date dimension include a Year column? (If not, create one using the LEFT function)

Create a measure:

Previous Year Sales = CALCULATE([Sales],

FILTER(ALL(date dimension[Year]),

date dimension[Year] = MAX(date dimension[Year]) -1)) 

[meddojeddo]

@PaulDBrown Yes I have a year column and this is the measure I would use for LY, however I need it to calculate YTD when it is calculating it for the current year's LY value(the 2020 row). That is what I am having trouble with. 

[PaulDBrown]

@meddojeddo 

OK, How about this?

Combined Measure = 
VAR PreviousYearSales = CALCULATE([Sales],
FILTER(ALL(date dimension[Year]),
date dimension[Year] = MAX(date dimension[Year]) -1))
VAR currYearMonth = YEAR(TODAY()) * 100 + MONTH(TODAY())
VAR _YTDSales = CALCULATE([Sales],
FILTER(date dimension,
date dimension [year] = YEAR(TODAY())
&&
date dimension [YearMonth] <= currYearMonth))
RETURN
IF(SELECTEDVALUE(date dimension [year]) = YEAR(TODAY()), _YTDSales, PreviousYearSales)

[meddojeddo]

I got rid of the error by using the value() function on every field in the calculation. However, I realized this calculates the current year's YTD sales in the current year row. I actually want it to calculate last year's YTD sales based on the same period we are in in the current year. 

EDIT: Ok, I have gotten it to work, by simply subtracting 1 from the year and 100 from the period for the current year variable.

[PaulDBrown]

@meddojeddo 

I'm glad it worked out, and apologies since I understood you wanted the current YTD for the current year and not the sales up to current month for last year on the current year row. (which of course makes more sense than what I was interpreting - sorry about that)

[meddojeddo]

@PaulDBrown well I thought it was working, but it doesn't appear to be. It seems that it returns a constant value in parenthesis(100) and it does not change with filters like the rest of the rows. 

EDIT: Actually, I had subracted the value in the wrong place. But when I do it in the right place, I get no result. I guess the filter function is filtering out the values in the 2020 row.

[PaulDBrown]

@meddojeddo 

I've modified the measure to get you last year's sales "to date":

Combined Measure = 
VAR PreviousYearSales = CALCULATE([Sales],
FILTER(ALL(date dimension[Year]),
date dimension[Year] = MAX(date dimension[Year]) -1))
VAR PYYearMonth = (YEAR(TODAY())-1)* 100 + MONTH(TODAY())
VAR _PYTDSales = CALCULATE([Sales],
FILTER(date dimension,
date dimension [year] = YEAR(TODAY())-1
&&
date dimension [YearMonth] <= _PYYearMonth))
RETURN
IF(SELECTEDVALUE(date dimension [year]) = YEAR(TODAY()), _PYTDSales, PreviousYearSales)

See if that works for you 

[meddojeddo]

@PaulDBrown it is not returning any data for the 2020 row. I just want to note that the year and period are in the fact table, I am not using a separate date table. Would this only work if there is a related date table? Would I have to create a date table with year and period and create a relationship between the two tables in order for this solution to work?

[PaulDBrown · Accepted solution]

@meddojeddo 

Oh. So what fields are in your 'date dimension' then? Or is 'date dimension' your fact table?

Can you post an image of your dimension table, fact table and model?

Otherwise you might try:

Combined Measure = 
VAR PreviousYearSales = CALCULATE([Sales],
FILTER(ALL(date dimension[Year]),
date dimension[Year] = MAX(date dimension[Year]) -1))
VAR PYYearMonth = (YEAR(TODAY())-1)* 100 + MONTH(TODAY())
VAR _PYTDSales = CALCULATE([Sales],
FILTER(ALL(date dimension),
date dimension [year] = YEAR(TODAY())-1
&&
date dimension [YearMonth] <= _PYYearMonth))
RETURN
IF(SELECTEDVALUE(date dimension [year]) = YEAR(TODAY()), _PYTDSales, PreviousYearSales)

[meddojeddo]

@PaulDBrown The date fields are in my fact table, so I replaced 'date dimension' with my fact table. I cannot post a screenshot due to sensitive data, but my table contains a Period field in the 202001, 202002, etc. format and a Year field with the year (2020) extracted from that.

I tried creating a date dimension table with each unique Period value and the associated Year, but when I replaced my fact table with DimDate in the above calculation, I have gotten only the 2019 row populated in the chart.

[PaulDBrown]

@meddojeddo

Did you try the last alternative measure I put in my last answer?
Without this last measurement has worked, I recommend that you make a period dimension table (with the YYYYMM and Year fields and use it as I put you in my last answer and in the visualization.

[meddojeddo]

@PaulDBrown The last calculation works without the dimension table, except the only problem is the slicers to not filter the 2020 row.

[PaulDBrown]

@meddojeddo 

Sorry, what do you mean by "the slicers to not filter the 2020 row"?

[meddojeddo]

@PaulDBrown sorry, I meant my report page has a slicer that applies filters based on another dimension, and when I use the last calculation, the slicer does not filter the _PYTDSales value in the 2020 row in the chart. It is because of the filter(all()) function in the variable calculation. But when remove the all() part, the _PYTDSales value in the 2020 row comes back blank.

[PaulDBrown · Accepted solution]

@meddojeddo 

I see. Ok, TRY changing the ALL FOR ALLSELECTED 

[meddojeddo]

@PaulDBrown ok THERE we go. Now it works. Thanks a bunch, this was very helpful!

[PaulDBrown]

@meddojeddo 

Great! Glad we got there eventually!

[meddojeddo]

@PaulDBrown I am getting an error saying "DAX comparison operators do not support comparing values of type Text with values of type Integer." I have gotten this type of error before. I am not sure why though, my Year and Period fields are set to Whole Number, so I am not sure which comparison it is talking about. Does anything get converted into text in these functions?