Dynamic calculated Column based on Dates

[ugux]

Hi,

I have a Dates table, and I added a Calculated column (called Period) based on YearQuarter column. This calc. column always show the last 4 Quarters, Last4_Quarter-1...etc, it is rolling Years we can say. It works fine now, and I created a lot of visuals and tables based on this "Period" column.

The problem is, that users want to change which is the ACT_Year, ACT_Year-1 ...etc...so they don't want to work the current data every time. For example they want to compare 2020 and 2019, but now in 2021_Q1 I can't do this.

I tried to use paramter but Calculated column not support paramters.

I tried with measure, and I can calculate ACT_Year, ACT_YEAR-1..etc with measure based on a Parameter, but it works only in I add the YearQuarter column too to the Table visual. But finally I want to summarize data only for ACT_YEAR,-1,-2..etc without any more detailed dates column.

How Can I do this, to let users to change the Period data?

I made the calculated column now like this where max_date is a measure always shows the max date in dates table:

Period =

var _MonthDiff = DATEDIFF(Dates[Date],max_date,MONTH)

Return

SWITCH(

TRUE(),

_MonthDiff>47,"ACT-4",

_MonthDiff>35,"ACT-3",

_MonthDiff>23,"ACT-2",

_MonthDiff>11,"ACT-1",

_MonthDiff>=0,"ACT")

Thank you in advance

[amitchandak · Accepted solution]

@ugux , You can pass filter value to calculated column,

You can use time intelligence with date table and create measures

example

Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing 4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

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

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA