cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tevisyauw
Helper I
Helper I

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

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 

InvoiceCustomerTransaction DateDue DateOverdue Date Value 
INV001Akai4/22/20224/30/2022182       1,000.00
INV002Bim4/22/20224/30/2022182       1,500.00
INV003Della4/22/20224/30/2022182       1,000.00
INV004Akai4/22/20224/30/2022182       6,000.00
INV005Akai4/22/20224/30/2022182     75,000.00
INV006Cerry6/29/20227/7/2022114       2,500.00
INV007Foam6/29/20227/7/2022114       8,000.00
INV008Della6/29/20227/7/2022114       8,500.00
INV009Cerry9/22/20229/30/202229       2,500.00
INV010Gero9/22/20229/30/202229       1,300.00
INV011Ian9/22/20229/30/202229       1,325.00
INV012Ian9/22/20229/30/202229       4,600.00
INV013Gero10/21/202210/29/20220       4,500.00
INV014Della10/21/202210/29/20220       5,500.00
INV015Foam10/22/202210/30/2022-1     10,250.00
INV016Foam11/24/202212/2/2022-34     15,000.00
INV017Foam11/24/202212/2/2022-34     16,000.00
INV018Cerry11/24/202212/2/2022-34     25,000.00
INV019Cerry11/26/202212/4/2022-36     17,500.00
INV020Gero11/26/202212/4/2022-36     90,000.00

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

 

expected Matrix table that I need looks like this 

CustomerTotal Value< 1 day1 - 30 days31 - 60 days> 60 days
Akai               82,000.00000                82,000.00
Bim1500000                   1,500.00
Cerry               47,500.00         42,500.00             2,500.000                   2,500.00
Della               15,000.00            5,500.0000                   9,500.00
Foam               49,250.00         41,250.0000                   8,000.00
Gero               95,800.00         94,500.00             1,300.0000
Ian                 5,925.000             5,925.0000
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

1 ACCEPTED SOLUTION
v-kalyj-msft
Community Support
Community Support

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.

vkalyjmsft_0-1669794034196.png

vkalyjmsft_3-1669794260129.png

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:

vkalyjmsft_2-1669794244611.png

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

vkalyjmsft_4-1669794375777.png

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.

View solution in original post

3 REPLIES 3
v-kalyj-msft
Community Support
Community Support

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.

vkalyjmsft_0-1669794034196.png

vkalyjmsft_3-1669794260129.png

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:

vkalyjmsft_2-1669794244611.png

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

vkalyjmsft_4-1669794375777.png

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.

Thank you @v-kalyj-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
Helper I
Helper I

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.