Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Dynamic Date Tables and Filters - keeping 1 measure

I am an advanced Tableau user who has recently started using PowerBI, and I am really struggling with being able to use date filters and comparison filters effectively!


What I want is a few slicers across my dashboard, so that the user can filter on:

-Time Span : YTD/HTD/QTD/MTD/WTF/Full Year/H1/H2/Q1/Q2/Q2/Q3/Q4

-Comparison Period : Year on Year/Period on Period

-Focus Year : 2017-2021

-Compare to Year : 2017-2021

without needing to make a new measure for every single time span, and is really clear on what dates we are looking at .


What I've set up as a dynamic date table does nearly all of the above (snippet of table code as follows so you can see the idea):

Selection Data = 
VAR YesterdayDate = (today()-1)
VAR YearStart = date(year(today()-1),1,1)
VAR HalfStart = date(year(today()-1),IF(month(today()-1)<=6,1,IF(month(today()-1)<=12,6,0)),1)
VAR QuarterStart = date(year(today()-1),IF(month(today()-1)<=3,1,IF(month(today()-1)<=6,4,IF(month(today()-1)<=9,7,IF(month(today()-1)<=12,10,0)))),1)
VAR YesterdayDateLY = (date(year(today()-365),month(today()-1),day(today()-1)))
VAR YearStartLY = date(year(today()-365),1,1)
VAR HalfStartLY = date(year(today()-365),IF(month(today()-1)<=6,1,IF(month(today()-1)<=12,6,0)),1)
VAR QuarterStartLY = date(year(today()-365),IF(month(today()-1)<=3,1,IF(month(today()-1)<=6,4,IF(month(today()-1)<=9,7,IF(month(today()-1)<=12,10,0)))),1)
VAR YearStartPP = date(year(today()-365),1,1)
VAR HalfStartPP = date(if(month(today()-1)<=6,year(today()-365),year(today()-1)),IF(month(TODAY()-1)<=6,6,1),1)
VAR QuarterStartPP = date(if(month(today()-1)<=3,year(today()-365),year(today()-1)),IF(month(today()-1)<=3,10,IF(month(today()-1)<=6,1,IF(month(today()-1)<=9,4,IF(month(today()-1)<=12,7,0)))),1)

VAR Result = 
ADDCOLUMNS(CALENDAR(YearStart , YesterdayDate) , "Selection", "YTD", "Order", 1, "Comparison Period", "YOY", "Time Span", year(YearStart), "Comp Type", "Current"), 
ADDCOLUMNS(CALENDAR(YearStartLY , YesterdayDateLY) , "Selection", "YTD", "Order", 1, "Comparison Period", "YOY", "Time Span", year(YearStartLY), "Comp Type", "Prior"),
ADDCOLUMNS(CALENDAR(HalfStart , YesterdayDate) , "Selection", "HTD", "Order", 2, "Comparison Period", "YOY", "Time Span", year(HalfStart), "Comp Type", "Current"), 
ADDCOLUMNS(CALENDAR(HalfStartLY , YesterdayDateLY) , "Selection", "HTD", "Order", 2, "Comparison Period", "YOY", "Time Span", year(HalfStartLY), "Comp Type", "Prior"), 
ADDCOLUMNS(CALENDAR(QuarterStart , YesterdayDate) , "Selection", "QTD", "Order", 3, "Comparison Period", "YOY", "Time Span", year(QuarterStart), "Comp Type", "Current"), 
ADDCOLUMNS(CALENDAR(QuarterStartLY , YesterdayDateLY) , "Selection", "QTD", "Order", 3, "Comparison Period", "YOY", "Time Span", year(QuarterStartLY), "Comp Type", "Prior"), 
ADDCOLUMNS(CALENDAR(YearStart , YesterdayDate) , "Selection", "YTD", "Order", 1, "Comparison Period", "POP", "Time Span", "THIS YEAR", "Comp Type", "Current"), 
ADDCOLUMNS(CALENDAR(YearStartPP , YearStartPP+(YesterdayDate-YearStart)) , "Selection", "YTD", "Order", 1, "Comparison Period", "POP", "Time Span", "PREVIOUS YEAR", "Comp Type", "Prior"),
ADDCOLUMNS(CALENDAR(HalfStart , YesterdayDate) , "Selection", "HTD", "Order", 2, "Comparison Period", "POP", "Time Span", "THIS HALF", "Comp Type", "Current"), 
ADDCOLUMNS(CALENDAR(HalfStartPP , HalfStartPP+(YesterdayDate-HalfStart)) , "Selection", "HTD", "Order", 2, "Comparison Period", "POP", "Time Span", "PREVIOUS HALF", "Comp Type", "Prior"), 
ADDCOLUMNS(CALENDAR(QuarterStart , YesterdayDate) , "Selection", "QTD", "Order", 3, "Comparison Period", "POP", "Time Span", "THIS QUARTER", "Comp Type", "Current"), 
ADDCOLUMNS(CALENDAR(QuarterStartPP , QuarterStartPP+(YesterdayDate-QuarterStart)) , "Selection", "QTD", "Order", 3, "Comparison Period", "POP", "Time Span", "PREVIOUS QUARTER", "Comp Type", "Prior"),


This allows 2/3 slicers as above and has the benefit of:

-My measures are set to calcuate over the "Comp Type" field so I have 1 measure per calc (often total sales, % of total and % diff over previous time span)

-The Legend automatically updates to "Time Span" field.


What I am struggling with is the year input - this obviously only works for 2021 v 2020 and I'd like the end user to have 2 drop down slicers to choose which 2 years they'd like to focus on.


I can't figure out a way to do this without needing to somehow make the "Comp Type" Dynamic (I guess using smaller year as prior, larger year as current - but I dont think it can work like this?), or use further ADDCOLUMNS for each permutation of the year combinations (w a column of Y1 and Y2). 

As this date table is already huge and slightly laggy to work with, and it's only February I am concerned about the second option.


I have experimented with using 2 What-If parameters as the Date(year( input driver in the variable setup (so tried to use them in the same way as a Tableau parameter) but this doesn't seem to work- I get 2017 - 2021 listed perfectly in the parameter table, but the dates that pull through in the Selection Data table are 1899-1900.  I've tried VALUES, VALUE, SELECTEDVALUE, MIN, MAX in additional columns/measures to try and make this work but no luck.


I've also tried using the timeline add in which almost has what I want from a user end functionality but seems to require multiple measures again.


I'd be grateful to hear anyone's suggestions - I know I probably need to come round to another way of thinking, but I just can't seem to figure out how people do any useful analysis over time!


Thanks so much in advance.


Frequent Visitor

This seems like a really complex solution! not sure if this will be much help but its always my go to when I'm dealing with time intielligence. good luck


REDUCE the # of measures with Calculation Groups In Power BI - YouTube

Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!


Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors