Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Create cumulative measure with several filters

Hi everyone !

 

I need your help to create a measure : 

 

Here is my raw data : 

RAW Data

Payment dateDelivery Date# Waiting daysUnity SoldProductColour
2023-05-092023-05-08-14CarRed
2023-05-082023-05-080918ChairBlue
2023-05-072023-05-081584PenOrange
2023-05-062023-05-082169HouseYellow
2023-05-052023-05-083441TablePurple
2023-05-042023-05-084291CarRed
2023-05-032023-05-085364ChairRed
2023-05-022023-05-086348PenBlue
2023-05-012023-05-087330HouseGreen
2023-04-302023-05-088110TableBlue
2023-04-292023-05-08970CarOrange
2023-04-282023-05-0810201ChairRed
2023-04-272023-05-0811591CarOrange
2023-04-262023-05-0812243CarBlue
2023-04-252023-05-0813167ChairRed
2023-04-232023-05-0815426PenRed
2023-04-212023-05-0817121CarBlue
2023-04-192023-05-0819321ChairOrange
2023-04-172023-05-0821189PenYellow
2023-04-142023-05-0824142HousePurple
2023-04-102023-05-0828221TableRed
2023-04-032023-05-0835245CarRed
2023-03-272023-05-0842373ChairBlue
2023-03-202023-05-0849255PenGreen
2023-03-132023-05-0856208HouseBlue
2023-03-062023-05-0863154TableOrange
2023-02-202023-05-0877190CarRed
2023-02-062023-05-0891193HouseOrange
2023-01-092023-05-0811954TableBlue
2022-12-122023-05-081476CarRed
2022-11-142023-05-081757HouseRed
2022-10-172023-05-082033TableBlue
2022-08-222023-05-082594CarOrange
2023-05-102023-05-09-14TableYellow
2023-05-092023-05-0901288CarPurple
2023-05-082023-05-0911128ChairRed
2023-05-072023-05-092253PenRed
2023-05-062023-05-093121HouseBlue
2023-05-052023-05-094502TableGreen
2023-05-042023-05-095374CarBlue
2023-05-032023-05-096473HouseOrange
2023-05-022023-05-097338TableRed
2023-05-012023-05-098509CarOrange
2023-04-302023-05-09956HouseBlue
2023-04-292023-05-091090TableRed
2023-04-282023-05-0911157CarRed
2023-04-272023-05-0912620TableBlue
2023-04-262023-05-0913149CarOrange
2023-04-242023-05-0915600ChairYellow
2023-04-222023-05-091788PenPurple
2023-04-202023-05-0919307HouseRed
2023-04-182023-05-0921134TableRed
2023-04-152023-05-0924105CarBlue
2023-04-112023-05-0928193HouseGreen
2023-04-042023-05-0935305TableBlue
2023-03-282023-05-0942221CarOrange
2023-03-212023-05-0949192HouseRed
2023-03-142023-05-0956122TableOrange
2023-03-072023-05-0963105CarBlue
2023-02-212023-05-0977112TableRed
2023-02-072023-05-0991234CarRed
2023-01-102023-05-0911936ChairBlue
2022-12-132023-05-0914711PenOrange
2022-10-182023-05-092034HouseYellow
2022-08-232023-05-092594TablePurple
2023-05-102023-05-1001472CarRed
2023-05-092023-05-101974HouseRed
2023-05-082023-05-102906TableBlue
2023-05-072023-05-103190CarGreen
2023-05-062023-05-104121HouseBlue
2023-05-052023-05-105434TableOrange
2023-05-042023-05-106370CarRed
2023-05-032023-05-107357TableOrange
2023-05-022023-05-108334CarBlue
2023-05-012023-05-109379ChairRed
2023-04-302023-05-101095PenRed
2023-04-292023-05-101145HouseBlue
2023-04-282023-05-1012228TableOrange
2023-04-272023-05-1013603CarYellow
2023-04-252023-05-1015585HousePurple
2023-04-232023-05-1017192TableRed
2023-04-212023-05-1019290CarRed
2023-04-192023-05-1021204HouseBlue
2023-04-162023-05-1024250TableGreen
2023-04-122023-05-1028187CarBlue
2023-04-052023-05-1035290TableOrange
2023-03-292023-05-1042220CarRed
2023-03-222023-05-1049265ChairOrange
2023-03-152023-05-1056139PenBlue
2023-03-082023-05-1063142HouseRed
2023-02-222023-05-1077158TableRed
2023-02-082023-05-1091166CarBlue
2023-01-112023-05-1011923HouseOrange
2022-12-142023-05-1014711TableYellow
2022-11-162023-05-101753CarPurple
2022-10-192023-05-102031HouseRed
2022-09-212023-05-102311TableRed

 

What I need to do is to create a measure that would allow me for every #waiting days to see the sum of sales inferior or equal to this #waiting days, that were paid on or after 2023-04-05, and only for delivery date for which there could have been sales at the specified #waiting days

Here is a view based on the above data with a measure calculating only sales paid on or after 2023-04-05 :

Delivery Date# Waiting daysSales filtered
2023-05-08-14
2023-05-080918
2023-05-081584
2023-05-082169
2023-05-083441
2023-05-084291
2023-05-085364
2023-05-086348
2023-05-087330
2023-05-088110
2023-05-08970
2023-05-0810201
2023-05-0811591
2023-05-0812243
2023-05-0813167
2023-05-0815426
2023-05-0817121
2023-05-0819321
2023-05-0821189
2023-05-0824142
2023-05-0828221
2023-05-09-14
2023-05-0901288
2023-05-0911128
2023-05-092253
2023-05-093121
2023-05-094502
2023-05-095374
2023-05-096473
2023-05-097338
2023-05-098509
2023-05-09956
2023-05-091090
2023-05-0911157
2023-05-0912620
2023-05-0913149
2023-05-0915600
2023-05-091788
2023-05-0919307
2023-05-0921134
2023-05-0924105
2023-05-0928193
2023-05-1001472
2023-05-101974
2023-05-102906
2023-05-103190
2023-05-104121
2023-05-105434
2023-05-106370
2023-05-107357
2023-05-108334
2023-05-109379
2023-05-101095
2023-05-101145
2023-05-1012228
2023-05-1013603
2023-05-1015585
2023-05-1017192
2023-05-1019290
2023-05-1021204
2023-05-1024250
2023-05-1028187
2023-05-1035290


My expected result is : 

# Waiting daysExpected Measure
-18
03686
16372
27700
38452
49366
510538
611729
712757
813707
914212
1012598
1115391
1216482
1317401
1519012
1719413
1920331
2120858
2421355
2821956
358506

 

The way the measure should work is for every # waiting days :

  • Sum the sales that have an equal or inferior number of # waiting days (For instance at #waiting days 19, I need to see all the sales for which the number of waiting days is 19 or less)
  • Only taking into account sales that were paid on or after 2023-04-05
  • But also only taking into account delivery dates on which delivery at this specific #waiting days were possible with payment on or after 2023-04-05. For instance, when I look at #waiting days 35, I only want sales with delivery date on or after 2023-05-10 (there are 35 days between 2023-04-05 and 2023-05-10, When I look at #waiting days 42, I only want sales with delivery date on or after 2023-05-17. That is why in the previous example, #waiting days 35 only includes delivery made on 2023-05-10 (it is the only delivery date in the example on or after 2023-05-10).

 

I am completely stuck here,

Thank you in advance !

jlwork_0-1685127591747.png

 

 

2 REPLIES 2
v-rzhou-msft
Community Support
Community Support

Hi @Anonymous ,

 

I suggest you to try code as below to create a measure.

Running Total = 
VAR _SUM1 =
    CALCULATE (
        SUM ( 'Table'[Unity Sold] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Payment date] >= DATE ( 2023, 04, 05 )
                && 'Table'[# Waiting days] <= MAX ( 'Table'[# Waiting days] )
        )
    )
VAR _SUM2 =
    CALCULATE (
        SUM ( 'Table'[Unity Sold] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Payment date] >= DATE ( 2023, 04, 05 )
                && 'Table'[# Waiting days] <= MAX ( 'Table'[# Waiting days] )
                && 'Table'[Delivery Date]
                    = DATE ( 2023, 04, 05 ) + 35
        )
    )
RETURN
    IF (
        MAX ( 'Table'[# Waiting days] ) <= 35,
        IF ( MAX ( 'Table'[# Waiting days] ) < 35, _SUM1, _SUM2 )
    )

Result is as below.

vrzhoumsft_0-1685342887677.png

 

Best Regards,
Rico Zhou

 

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

Anonymous
Not applicable

Hello  ! Thank you for your answer ! 🙂

It is helping but the limitation on number of dates being taken into account only works for #waiting days 35, I was just wondering what could be change for this to work not only on #waiting days 35, but for all #waiting days ?

  • When I look at #waiting days 35, I only want sales with delivery on or after 2023-05-10 (there are 35 days between 2023-04-05 and 2023-05-10)
  • When I look at #waiting days 42, I only want sales with delivery on or after 2023-05-17 (there are 42 days between 2023-04-05 and 2023-05-17)
  • etc.

I have modified the initial post so it could be clearer.

For instance, if I add theses ligns to the initial data:

2023-04-182023-05-3042100TableGreen
2023-05-302023-05-3011TableGreen

The expected result would be :

# Waiting days Expected Measure

-18
03687
16373
27701
38453
49367
510539
611730
712758
813708
914213
1012599
1115392
1216483
1317402
1519013
1719414
1920332
2120859
2421356
2821957
358507
42101

Thank you !

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.