topic Re: Using a filter for custom date periods, how I display 2 separate graphs with 2 different time period in DAX Commands and Tips

Using a filter for custom date periods, how I display 2 separate graphs with 2 different time period

MyWeeLola — Tue, 18 Jun 2024 11:04:51 GMT

Good morning

I have a code that utilizes 'addcolumns' to create custom time periods in an easily accessible way and is accessed using a slicer.

An example of the code is displayed at the end bottom of the post.

Currently, I have a line graph that displays data with the x axis being a date range which is filtered using a slicer.

This works in a fairly standard way and works well.

If a user selects, "last 7 days" then the last 7 days of data is displayed.

This is replicated for selections like 'last 14 days', mtd, qtd, last qtr, ytd, last year.

What I now need is if a user selects 'last 7 days' the graph continues to display the last 7 days, then a second graph displays the previous 7 days before that. That methodology is selected for the other time ranges too. Given the nature of the data being displayed, this would provide an easier way to compare performance.

How would I adapt my code to accommodate this?

here is the code of the 'date periods' selection I use.

I look forward to any assistance

Date Periods =

var _sd = min('Date'[Date])

var _ed = max('Date'[Date])

VAR __Date = TODAY()

VAR __NextMonth = EOMONTH(__Date,-1)

VAR __Month = MONTH(__NextMonth)

VAR __Year = YEAR(__NextMonth)

VAR __Quarter = QUARTER(__Date)

VAR __QYear = SWITCH(__Quarter,1,YEAR(__Date) - 1,YEAR(__Date))

VAR __MonthStart = SWITCH(__Quarter,1,10,2,1,3,4,7)

VAR __MonthEnd = SWITCH(__Quarter,1,12,2,3,3,6,9)

VAR __Day = SWITCH(__Quarter,1,31,2,31,3,30,30)

VAR __PYear = YEAR(__Date) - 1

RETURN

UNION(

ADDCOLUMNS(

DATESMTD('Date'[Date]),

"Type","MTD",

"Order",5

ADDCOLUMNS(

DATESQTD('Date'[Date]),

"Type","QTD",

"Order",7

ADDCOLUMNS(

CALENDAR(_ed-6,_ed),

"Type","Last 7 Days",

"Order",1

ADDCOLUMNS(

CALENDAR(_ed-13,_ed),

"Type","Last 14 Days",

"Order",3

ADDCOLUMNS(

CALENDAR(DATE(__Year,__MonthStart,1),DATE(__Year,__MonthEnd,__Day)),

"Type","Last Qtr",

"Order",8

ADDCOLUMNS(

CALENDAR(DATE(__PYear,1,1),DATE(__PYear,12,31)),

"Type","Last Year",

"Order",10

ADDCOLUMNS(

datesytd('Date'[Date]),

"Type","This Year",

"Order",9

ADDCOLUMNS(

CALENDAR(_sd,_ed),

"Type","All time",

"Order",11

ADDCOLUMNS(

CALENDAR(_sd,_ed),

"Type","Custom",

"Order",12

ADDCOLUMNS(

CALENDAR(DATE(__Year,__Month,1),__NextMonth),

"Type","Last Month",

"Order",6

ADDCOLUMNS(

CALENDAR(_ed-27,_ed-14),

"Type","Previous 14 Days",

"Order",4

ADDCOLUMNS(

CALENDAR(_ed-13,_ed-7),

"Type","Previous 7 Days",

"Order",2

)

Re: Using a filter for custom date periods, how I display 2 separate graphs with 2 different time period

Anonymous — Wed, 19 Jun 2024 01:19:56 GMT

HI MyWeeLola ,

You may need to create a measure formula with switch function to check the current date period type and redirect to the specific formula to show the previous period values.(each period has its own previous calculation expressions)

Write Conditional Statement Using SWITCH in DAX and Power BI - RADACAD

Regards,

Xiaoxin Sheng

Re: Using a filter for custom date periods, how I display 2 separate graphs with 2 different time period

MyWeeLola — Wed, 19 Jun 2024 13:14:30 GMT

Anonymous

Thank you for the response.

Would the following work in principle?

On the result of the switch statement, I could set a min and max date. I would then either create or filter a second date table based on those values, and then apply at date range to the second graph.

What would bite me using that methodology?

Lola

Re: Using a filter for custom date periods, how I display 2 separate graphs with 2 different time period

Anonymous — Thu, 20 Jun 2024 06:10:28 GMT

HI MyWeeLola,

In fact, you not need to create a new table. The switch function are used to confirm the period duration which will be used in the calculate expressions.

For example:

The MTD the period duration is month, the YTD is year, QTD is quarter.

For above scenario, you can add a variable with switch function to convert them to days, then you can use this as condition in expressions.

Regards,

Xiaoxin Sheng

Re: Using a filter for custom date periods, how I display 2 separate graphs with 2 different time period

MyWeeLola — Mon, 01 Jul 2024 11:24:43 GMT

Anonymous

Thank you for the response. I got it working with the second table. That you for your time. It is appreciated.

Lola