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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Year-over-Year Data without Dates

I'm trying to use DAX to display year-over-year changes. In my situation, I don't think I can use time intelligence functions because my data doesn't exactly correspond to calendar months and days. 

 

This is my source data. Each fiscal period is a month long, but starts on the 26th instead of the 1st. The business day indicates how many business days (non-holiday weekdays) have been completed during this fiscal period. The cumulative amount is the sum from the beginning of the period to the current business day. 

Source DataSource Data

 

My goal is to create a table like the following, where a user can select a fiscal period via slicer (example: 201803) and see, for each business day, the current year's amount and last year's amount for the same period and business day. You can get last year's fiscal period by subtracting 100 from the current fiscal period: (201803 - 100 = 201703). Note that in some cases, the months will have a different number of business days. 

GoalGoal




goal_graph.PNG

 

  I also have a calendar of dates in a format similar to the following (I simplified these data views):

 calendar_table.PNG

 

Thus far, I haven't figured out how to do this. Here was one of my attempts:

Amount Last Year = 
VAR this_period = MAX(Calendar[Period])
VAR this_business_day = MAX(Calendar[Business_Day])
RETURN
CALCULATE(
    SUM('Data'[Cumul_Amount]),
    FILTER(Calendar,
    Calendar[Period]=this_period-100 && Calendar[Business_Day] = this_business_day
    )
 )

 

1 ACCEPTED SOLUTION
v-cherch-msft
Employee
Employee

Hi @Anonymous

 

You may use ALLSELECTED Function (DAX). You may try below measure:

Amount Last Year =
VAR this_period =
    MAX ( Calendar[Period] )
VAR this_business_day =
    MAX ( Calendar[Business_Day] )
RETURN
    CALCULATE (
        SUM ( 'Data'[Cumul_Amount] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            Calendar[Period]
                = this_period - 100
                && Calendar[Business_Day] <= this_business_day
        )
    )

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-cherch-msft
Employee
Employee

Hi @Anonymous

 

You may use ALLSELECTED Function (DAX). You may try below measure:

Amount Last Year =
VAR this_period =
    MAX ( Calendar[Period] )
VAR this_business_day =
    MAX ( Calendar[Business_Day] )
RETURN
    CALCULATE (
        SUM ( 'Data'[Cumul_Amount] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            Calendar[Period]
                = this_period - 100
                && Calendar[Business_Day] <= this_business_day
        )
    )

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.