Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

New Measures - Weekly Current Year, Weekly Last Year, YTD Weekly Current and YTD Weekly Last Year

Hi there,   I have a transaction list. It runs on daily basis. I have it summarized by week ending #.    Something like this: Ticket Number | Date | Amount | Week Ending #   It could have mult...
Need Help
v-lionel-msft's avatar
v-lionel-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

Do you want to create the following four measures?

Please give example data for all used tables.

 

Best regards,
Lionel Chen

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
    6 years ago

    • v-lionel-msft's avatar
      v-lionel-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Anonymous ,

       

      Maybe you can do like this:

      Weekly Current Year Amount = 
CALCULATE(
    SUM(Sheet1[Amount]),
    FILTER(
        ALL(Sheet1),
        Sheet1[Year] = SELECTEDVALUE( Sheet1[Year]) && Sheet1[Week] = SELECTEDVALUE(Sheet1[Week])
    )
)
      Weekly Last Year Amount = 
CALCULATE(
    SUM(Sheet1[Amount]),
    FILTER(
        ALL(Sheet1),
        Sheet1[Year] = SELECTEDVALUE(Sheet1[Year]) - 1 && Sheet1[Week] = SELECTEDVALUE(Sheet1[Week])
    )
)
      YTD Weekly Current Year Amount = 
CALCULATE(
    SUM(Sheet1[Amount]),
    FILTER(
        ALLSELECTED(Sheet1),
        Sheet1[Year] = SELECTEDVALUE(Sheet1[Year]) && Sheet1[Week] <= SELECTEDVALUE(Sheet1[Week]) 
    )
)
      YTD Weekly Last year Amount = 
CALCULATE(
    SUM(Sheet1[Amount]),
    FILTER(
        ALLSELECTED(Sheet1),
        Sheet1[Year] = SELECTEDVALUE(Sheet1[Year])-1 && Sheet1[Week] <= SELECTEDVALUE(Sheet1[Week]) 
    )
)

       

      Best regards,
      Lionel Chen

      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
        6 years ago

        Lionel,

         

        Thank you for your suggestions. The YTD ones are giving me blanks. 

         

        Jen