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/m-p/373270

 

I have two tables - a Dates table with the following columns:

Date

Day (Sun-Sat)

Week (52 weeks in the year)

Period (13 periods in the year)

 

and a data table with the following columns

Date (connected to the Dates table)

GP TY (Gross Profit this year)

 

My original query was to show the period to date when selecting a specific week which gave me this formula:

GP TY (PTD) = SUMX( SUMMARIZE( FILTER( ALL(Dates) , Dates[Period] = MIN(Dates[Period] ) && Dates[Week] <= MAX(Dates[Week]) ) , Dates[Date] , "ABCD" , [GP TY] ) , [ABCD] )

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). I've added in a Day column to the summarized table as follows:

GP TY (PTD) NEW = SUMX( SUMMARIZE( FILTER( ALL(Dates) , Dates[Period] = MIN(Dates[Period] ) && Dates[Week] <= MAX(Dates[Week]) ) , Dates[Date] , Dates[Day] , "ABCD" , [GP TY] ) , [ABCD] )

but I don't know how to keep the Day selections in the FILTER function. I'd guess I need to add something like "&& Dates[Day] = VALUES(Dates[Day})" but that doesn't work.

 

Any help would be appreciated - hopefully I've explained it clearly!

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

5 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community 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
        Community 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