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

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

        Hi Anonymous ,

         

        It may be caused by the difference in our data model. In addition, did you add the ALLSELECTED() function to your formula?

         

        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.