Summarize Date table while keeping Day selections

[David4]

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!

[v-juanli-msft · Accepted solution]

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

[v-juanli-msft]

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]

Hi Maggie

I've uploaded an example file Here.

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

Week	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Total
1	88	689	650	133	390	31	582	2,563
2	895	821	983	202	10	618	416	3,945
3	66	453	931	97	997	445	163	3,152
4	751	518	143	203	842	26	911	3,394
5	387	116	662	285	616	488	209	2,763
6	497	283	653	505	120	15	791	2,864
7	83	170	460	54	63	919	301	2,050
8	230	489	426	904	729	178	801	3,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:

Week	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Total
1	2,563	2,563	2,563	2,563	2,563	2,563	2,563	2,563
2	6,508	6,508	6,508	6,508	6,508	6,508	6,508	6,508
3	9,660	9,660	9,660	9,660	9,660	9,660	9,660	9,660
4	13,054	13,054	13,054	13,054	13,054	13,054	13,054	13,054
5	2,763	2,763	2,763	2,763	2,763	2,763	2,763	2,763
6	5,627	5,627	5,627	5,627	5,627	5,627	5,627	5,627
7	7,677	7,677	7,677	7,677	7,677	7,677	7,677	7,677
8	11,434	11,434	11,434	11,434	11,434	11,434	11,434	11,434

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

Week	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Total
1	88	689	650	133	390	31	582	2,563
2	983	1,510	1,633	335	400	649	998	6,508
3	1,049	1,963	2,564	432	1,397	1,094	1,161	9,660
4	1,800	2,481	2,707	635	2,239	1,120	2,072	13,054
5	387	116	662	285	616	488	209	2,763
6	884	399	1,315	790	736	503	1,000	5,627
7	967	569	1,775	844	799	1,422	1,301	7,677
8	1,197	1,058	2,201	1,748	1,528	1,600	2,102	11,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

[Fcoatis]

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

[v-juanli-msft · Accepted solution]

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 · Accepted solution]

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