Forum Discussion

ryan_b_fiting's avatar
ryan_b_fiting
Post Patron
6 years ago
Solved

Dynamically 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 solu...
Data Protection
Need Help
Show and Tell
Tips and Tricks
  • Stachu's avatar
    Stachu
    6 years ago

    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

Stachu's avatar
Stachu
Community Champion
6 years ago

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's avatar
ryan_b_fiting
Post Patron
6 years ago

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

  • Stachu's avatar
    Stachu
    Community Champion
    6 years ago

    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

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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,