Forum Discussion
Post PatronDynamically change measure for first date
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
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
4 Replies
- Stachu
Community Champion
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_fitingStachuI 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
- Stachu
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