Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Week to Date Measure Grand Total not working

 

Hello PBI Community - 

I need some help, I'm creating a small dashboard with current month YTD, MTD, WTD compared to prior year. I'm having an issue with the WTD measure where its aggregate is not displaying. Any help would be greatly appreciated

 

Below is a screenshot and the measures I created

 

WTD Rev = IF (
    HASONEVALUE ( 'date'[year] )
        && HASONEVALUE ('date'[week number]),
    CALCULATE (
        [amount],
        FILTER (
            ALL ( 'date' ),
            'date'[year] = VALUES ('date'[year])
                && 'date'[week number] = VALUES ('date'[week number])
                && 'date'[Date] <= MAX ('date'[Date])
        )
    ),
    BLANK ()
)

 

 

 

WTD LY Rev = IF (
    HASONEVALUE ( 'date'[year] )
        && HASONEVALUE ('date'[week number]),
    CALCULATE (
        SUM('reporting ops_dashboard'[amt]),
        FILTER (
            ALL('date'),
            'date'[year] = VALUES ('date'[year])-1
                && 'date'[week number] = VALUES ('date'[week number])
                && 'date'[Date] <= MAX ('date'[Date])
        )
    ),
    BLANK ()
)

 

 

 

 

Thank you 

Mike

  • Hi Anonymous ,

     

    You can create measures like DAX below.

     

    WTD Rev =CALCULATE (
           [amount],
           FILTER (
               ALLSELECTED ( 'date' ),
               'date'[year] = MAX('date'[year])
                   && 'date'[week number] = MAX('date'[week number])))
     
     
    WTD LY Rev = CALCULATE (
           [amount],
           FILTER (
               ALLSELECTED ( 'date' ),
               'date'[year] = MAX('date'[year]-1)
                   && 'date'[week number] = MAX('date'[week number])))

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support

    Hi Anonymous ,

     

    You can create measures like DAX below.

     

    WTD Rev =CALCULATE (
           [amount],
           FILTER (
               ALLSELECTED ( 'date' ),
               'date'[year] = MAX('date'[year])
                   && 'date'[week number] = MAX('date'[week number])))
     
     
    WTD LY Rev = CALCULATE (
           [amount],
           FILTER (
               ALLSELECTED ( 'date' ),
               'date'[year] = MAX('date'[year]-1)
                   && 'date'[week number] = MAX('date'[week number])))

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thank you!