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

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

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

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:

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