need help to finding due date different with selected slicer date and group sales based on days

[tevisyauw]

Hi All,

I need help with my problem, thanks before.

I have problem with making chart over due days that automatically change based on selected slicer date (using Before function)

here is my excel table looks like 

Invoice	Customer	Transaction Date	Due Date	Overdue Date	Value
INV001	Akai	4/22/2022	4/30/2022	182	1,000.00
INV002	Bim	4/22/2022	4/30/2022	182	1,500.00
INV003	Della	4/22/2022	4/30/2022	182	1,000.00
INV004	Akai	4/22/2022	4/30/2022	182	6,000.00
INV005	Akai	4/22/2022	4/30/2022	182	75,000.00
INV006	Cerry	6/29/2022	7/7/2022	114	2,500.00
INV007	Foam	6/29/2022	7/7/2022	114	8,000.00
INV008	Della	6/29/2022	7/7/2022	114	8,500.00
INV009	Cerry	9/22/2022	9/30/2022	29	2,500.00
INV010	Gero	9/22/2022	9/30/2022	29	1,300.00
INV011	Ian	9/22/2022	9/30/2022	29	1,325.00
INV012	Ian	9/22/2022	9/30/2022	29	4,600.00
INV013	Gero	10/21/2022	10/29/2022	0	4,500.00
INV014	Della	10/21/2022	10/29/2022	0	5,500.00
INV015	Foam	10/22/2022	10/30/2022	-1	10,250.00
INV016	Foam	11/24/2022	12/2/2022	-34	15,000.00
INV017	Foam	11/24/2022	12/2/2022	-34	16,000.00
INV018	Cerry	11/24/2022	12/2/2022	-34	25,000.00
INV019	Cerry	11/26/2022	12/4/2022	-36	17,500.00
INV020	Gero	11/26/2022	12/4/2022	-36	90,000.00

Slicer Filter date that I choose : 10/29/2022 

expected Matrix table that I need looks like this 

Customer	Total Value	< 1 day	1 - 30 days	31 - 60 days	> 60 days
Akai	82,000.00	0	0	0	82,000.00
Bim	1500	0	0	0	1,500.00
Cerry	47,500.00	42,500.00	2,500.00	0	2,500.00
Della	15,000.00	5,500.00	0	0	9,500.00
Foam	49,250.00	41,250.00	0	0	8,000.00
Gero	95,800.00	94,500.00	1,300.00	0	0
Ian	5,925.00	0	5,925.00	0	0
TOTAL	296,975.00	183,750.00	9,725.00	-	103,500.00

I already find the solution if the Slicer date = Today,

But I cannot find the solution  if the slicer date is changeable to different date and the Over Due will follow the different between due date and slicer date

Please help me. thank you

[v-yanjiang-msft · Accepted solution]

Hi @tevisyauw ,

According to your description, here's my solution.

1.Create a table to list all the ranges, then sort the Range column by Sort column.

2.Create two measures.

Measure1 =
IF (
    HASONEVALUE ( 'Table'[Invoice] ),
    MAX ( 'Table'[Value] ),
    SUMX ( 'Table', 'Table'[Value] )
)

Measure2 =
SWITCH (
    MAX ( 'Range'[Range] ),
    "Total Value", SUM ( 'Table'[Value] ),
    "< 1 day",
        CALCULATE (
            [Measure1],
            DATEDIFF ( 'Table'[Due Date], SELECTEDVALUE ( 'Date'[Date] ), DAY ) < 1
        ) + 0,
    "1 - 30 days",
        CALCULATE (
            [Measure1],
            DATEDIFF ( 'Table'[Due Date], SELECTEDVALUE ( 'Date'[Date] ), DAY ) >= 1,
            DATEDIFF ( 'Table'[Due Date], SELECTEDVALUE ( 'Date'[Date] ), DAY ) <= 30
        ) + 0,
    "31 - 60 days",
        CALCULATE (
            [Measure1],
            DATEDIFF ( 'Table'[Due Date], SELECTEDVALUE ( 'Date'[Date] ), DAY ) >= 31,
            DATEDIFF ( 'Table'[Due Date], SELECTEDVALUE ( 'Date'[Date] ), DAY ) <= 60
        ) + 0,
    "> 60 days",
        CALCULATE (
            [Measure1],
            DATEDIFF ( 'Table'[Due Date], SELECTEDVALUE ( 'Date'[Date] ), DAY ) > 60
        ) + 0
)

Put Customer in Matrix Rows, Range in Columns and the new Measure2 in Values, get the correct result:

It can dynamically change by the selected value in Date slicer.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tevisyauw]

Thank you @v-yanjiang-msft , it worked like a magic. I just tweak it a little on Selected date from

 SELECTEDVALUE ( 'Date'[Date] )

to 

 LASTDATE( 'Date'[Date] )

because I'm using date slicer type Before

[tevisyauw]

need help. from anyone. the empty cell was 0 value. got auto formatted from excel