Forum Discussion

David4's avatar
David4
Frequent Visitor
8 years ago
Solved

Summarize Date table while keeping Day selections

Hi   I posted a question which was answered in the link below but I'm trying to refine it and am getting stuck   http://community.powerbi.com/t5/Desktop/Identifying-Period-number-from-Week-number...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi David4

    With this measure I can get the result as your example.

    Measure 4 =
CALCULATE (
    SUM ( GP[GP] ),
    FILTER (
        ALLSELECTED ( Dates ),
        'Dates'[Period] = MAX ( 'Dates'[Period] )
            && 'Dates'[Week] <= MAX ( 'Dates'[Week] )
            && 'Dates'[Day] = MAX ( 'Dates'[Day] )
    )
)

    Best Regards

    Maggie

  • David4's avatar
    David4
    8 years ago

    Thanks Maggie - I've tweaked your measure slightly so that the totals work:

     

    Measure 5 = 
CALCULATE (
    SUM ( GP[GP] ),
    FILTER (
        ALLSELECTED ( Dates ),
        'Dates'[Period] = MAX ( 'Dates'[Period] )
            && 'Dates'[Week] <= MAX ( 'Dates'[Week] )
    ),
    VALUES(Dates[Day])
)

    Thanks so much for your help with this

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi

 

I'd like to refine this so that it keeps any Day selections that are made using a slicer on the page (e.g. if someone wants to know how Tuesdays and Thursdays together in a period were performing)

Would you like to select both Tuesday and Thursday in a slicer, then the visual shows values of these two days in a period?

If it is in this case, you need turn off the Single Selected option as below.

 

 

 

“it keeps any Day selections” does it mean maintaining day selection while selecting a specific week

 


Best Regards

Maggie

  • David4's avatar
    David4
    Frequent Visitor
    8 years ago

    Hi Maggie

     

    I've uploaded an example file Here.

     

    The top table is my raw data which shows GP by day:

    WeekSundayMondayTuesdayWednesdayThursdayFridaySaturdayTotal
    188689650133390315822,563
    2895821983202106184163,945
    366453931979974451633,152
    4751518143203842269113,394
    53871166622856164882092,763
    6497283653505120157912,864
    78317046054639193012,050
    82304894269047291788013,757

     

    On the table titled "GP PTD - INCORRECT FOR DAYS" you can see what my current formula is giving me. At the moment it is showing the same value for each day in a week like this:

    WeekSundayMondayTuesdayWednesdayThursdayFridaySaturdayTotal
    12,5632,5632,5632,5632,5632,5632,5632,563
    26,5086,5086,5086,5086,5086,5086,5086,508
    39,6609,6609,6609,6609,6609,6609,6609,660
    413,05413,05413,05413,05413,05413,05413,05413,054
    52,7632,7632,7632,7632,7632,7632,7632,763
    65,6275,6275,6275,6275,6275,6275,6275,627
    77,6777,6777,6777,6777,6777,6777,6777,677
    811,43411,43411,43411,43411,43411,43411,43411,434

     

    What I'd like it to give is the period to date just for those specific days like this:

    WeekSundayMondayTuesdayWednesdayThursdayFridaySaturdayTotal
    188689650133390315822,563
    29831,5101,6333354006499986,508
    31,0491,9632,5644321,3971,0941,1619,660
    41,8002,4812,7076352,2391,1202,07213,054
    53871166622856164882092,763
    68843991,3157907365031,0005,627
    79675691,7758447991,4221,3017,677
    81,1971,0582,2011,7481,5281,6002,10211,434

     

    So Sunday in Week 3 is 1,049 because it is the 88 in Week 1 + 895 in Week 2 + 66 in Week 3

     

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      8 years ago

      Hi David4

      With this measure I can get the result as your example.

      Measure 4 =
CALCULATE (
    SUM ( GP[GP] ),
    FILTER (
        ALLSELECTED ( Dates ),
        'Dates'[Period] = MAX ( 'Dates'[Period] )
            && 'Dates'[Week] <= MAX ( 'Dates'[Week] )
            && 'Dates'[Day] = MAX ( 'Dates'[Day] )
    )
)

      Best Regards

      Maggie

      • David4's avatar
        David4
        Frequent Visitor
        8 years ago

        Thanks Maggie - I've tweaked your measure slightly so that the totals work:

         

        Measure 5 = 
CALCULATE (
    SUM ( GP[GP] ),
    FILTER (
        ALLSELECTED ( Dates ),
        'Dates'[Period] = MAX ( 'Dates'[Period] )
            && 'Dates'[Week] <= MAX ( 'Dates'[Week] )
    ),
    VALUES(Dates[Day])
)

        Thanks so much for your help with this

    • Fcoatis's avatar
      Fcoatis
      Icon for Post Patron rankPost Patron
      7 years ago

      Hi David4

       

      Sorry to ask this elementary question, but how do ypu paste this tables? I´m trying to paste tables copied this way but it goes all wrong.

       

      Thanks in advance