Dynamically change measure for first date

[ryan_b_fiting]

Hello Community - 

I am coming across an issue with a measure I have created.  It works as expected for all periods except the very first period, and I know why, I just need some help with a solution to fix it.

I have a productivity measure that is written like this:

Competitor Productivity (Units) = 
DIVIDE(
DIVIDE([Total Unit Sales],[Total Stores],0),
(DIVIDE([Days in Period],7,0) ),
0 )

This works great to get my trending productivity except for when I am filtering with a date filter that does not land exactly on the start/end of a week (I use relative date filters mostly so I encounter this issue all the time). 

My sales data is weekly (if this was daily I would not have the issue).  So when I am filtering for the last 6 months, if the week does not land exactly right, my days in the period are <7 but my Total Sales are for the entire 7 days which skews the first week of chart

as you can see in the visual below.  I have to use Days In Period because we look at the productivity on a weekly basis but also a monthly and quarterly basis (otherwise I could just take out the denominator of DIVIDE(Days In Period,7,0)

So my ask is, is there an easy variable or logic I can throw into my Productivity measure to dynamically adjust that first WEEK (week only) to exclude that DIVIDE(Days In Period, 7, 0 ) denominator, and bring it back if we drill up to months or quarters?

Thanks for you input!

Ryan

[Stachu]

I think the easies way would be to replace 7 in

(DIVIDE([Days in Period],7,0) )

with count of days in the given week, this way the measure is universal and will work fine for any edge cases - I assume you will have the same issue with last week of the year, correct?

[ryan_b_fiting]

@StachuI am not sure what you would me by counting the days in a given week?  Can you elaborate on that?  So, I always need the denominator to be 7 except for the weeks that are cut off.  So when climbing back up the hierarchy to month and quarter, the denominator still needs to be 7.  So would this type of solution work there too?

Thanks for the suggestion, once I understand a little better, hopefully I can implement it and it will work!

Ryan

[v-lid-msft]

Hi @ryan_b_fiting ,

We can try to use the following measure to meet your requirement:

Competitor Productivity (Units) =
VAR DaysFromFirstDay =
    DATEDIFF (
        CALCULATE ( MIN ( 'Table'[WeekEnding] ), ALLSELECTED ( 'Table'[WeekEnding] ) ),
        MIN ( 'Table'[WeekEnding] ),
        DAY
    )
RETURN
    DIVIDE (
        DIVIDE ( [Total Unit Sales], [Total Stores], 0 ),
        IF (
            DaysFromFirstDay < 7,
            DIVIDE ( [Days in Period], DaysFromFirstDay + 1, 0 ),
            DIVIDE ( [Days in Period], 7, 0 )
        ),
        0
    )

If it does not work, could you please share the formula of [Days in Period] after removing any confidential information?

Best regards,

[Stachu · Accepted solution]

how do you store the week identyfier in your data? If it's a date then you just can count the days in the calendar table

If it's something like W01, W02, etc.. yhen you can create reference table that would store the the value, e.g. like this:

W01	4
W02	7
W03	7
W04	7

this way you don't need and IF statement in your measure, you just reference value from the table