Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MyWeeLola
Helper I
Helper I

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

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
    )
)
1 ACCEPTED SOLUTION

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft 

 

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft 

 

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

 

Lola

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.