How to calculate Filterd VAR table by cumulative total

[ohnothimagain]

Hello everyone. I came back again...
I want to make a table filtered and summary values cumulative,but this doesn't work.How should I correct it?

Ftd_sum :=
VAR tbl = 
    SELECTCOLUMNS(
        FILTER(ALL('TEST_'), 'TEST_'[STS] = "〇"),
        "@Trg", 'TEST_'[PN]
    )
VAR Dis = 
    DISTINCT(tbl)   
VAR Ftd = 
    CALCULATETABLE(
        'TEST_',
        'TEST_'[PN] IN Dis,
        FILTER(
            ALLSELECTED('Calendar'),
            'Calendar'[Date] <= MAX('Calendar'[Date])
        ),
        USERELATIONSHIP('Calendar'[Date], 'TEST_'[Calculated])
    )

VAR Result = 
    CALCULATE(
        SUM('TEST_'[Qty]), 
        KEEPFILTERS(Ftd)
    )
      
RETURN 
    Result

Table (I'm doing it on powerpivot)

want it to be

Thank you for reading,and continuous support!
(Thank @AnonymousNinja for cooperation previously,but still unsuccess)

Best Regards,

[ahadkarimi]

Hey@ohnothimagain,
Try this code, and if you encounter any issues, let me know:

Ftd_sum :=
VAR tbl =
SELECTCOLUMNS(FILTER(ALL('TEST_'), 'TEST_'[STS] = "〇"),
"@Trg", 'TEST_'[PN])
VAR Dis =
DISTINCT(tbl)
VAR Ftd =CALCULATETABLE(
'TEST_',FILTER(ALLSELECTED('TEST_'),'TEST_'[PN] IN Dis))
VAR Result =CALCULATE(SUM('TEST_'[Qty]),

FILTER(ALLSELECTED('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date])),KEEPFILTERS(Ftd))
RETURN
Result

[ohnothimagain]

Thank you for your reply! I tried right away,definately got closer! (got cumulative total)
I have additional questions;

1. I need to add "use relationship" how should I write this..?

   following doesn't work.

var Ftd
=CALCULATETABLE('TEST_',
                  filter(allselected(TEST_),'TEST_'[PN] IN Dis),
                      USERELATIONSHIP('Calendar'[Date],'TEST_'[Calculated]))

 
2.If I need to calculate as the same way with distinct [PO]column,how should I do it?? 


Best regards,

[ahadkarimi]

Hi @ohnothimagain, go ahead and use this code, and if there are any issues, just let me know.

Ftd :=
    CALCULATETABLE(
        'TEST_',
        FILTER(ALLSELECTED('TEST_'), 'TEST_'[PN] IN Dis),
        USERELATIONSHIP('Calendar'[Date], 'TEST_'[Calculated])
    )

Then, use :

Ftd_sum :=
VAR tbl = 
    SELECTCOLUMNS(
        FILTER(ALL('TEST_'), 'TEST_'[STS] = "〇"),
        "@Trg", 'TEST_'[PN]
    )
VAR Dis = 
    DISTINCT(tbl)
VAR Ftd = 
    CALCULATETABLE(
        'TEST_',
        FILTER(ALLSELECTED('TEST_'), 'TEST_'[PN] IN Dis),
        USERELATIONSHIP('Calendar'[Date], 'TEST_'[Calculated])
    )
VAR Result = 
    CALCULATE(
        SUM('TEST_'[Qty]),
        FILTER(
            ALLSELECTED('Calendar'),
            'Calendar'[Date] <= MAX('Calendar'[Date])
        ),
        KEEPFILTERS(Ftd)
    )
RETURN 
    Result

Calculate distinct [PO]column:

Ftd_sum_PO :=
VAR tbl = 
    SELECTCOLUMNS(
        FILTER(ALL('TEST_'), 'TEST_'[STS] = "〇"),
        "@Trg", 'TEST_'[PO]  -- Change to the PO column
    )
VAR Dis = 
    DISTINCT(tbl)
VAR Ftd = 
    CALCULATETABLE(
        'TEST_',
        FILTER(ALLSELECTED('TEST_'), 'TEST_'[PO] IN Dis),
        USERELATIONSHIP('Calendar'[Date], 'TEST_'[Calculated])
    )
VAR Result = 
    CALCULATE(
        SUM('TEST_'[Qty]),
        FILTER(
            ALLSELECTED('Calendar'),
            'Calendar'[Date] <= MAX('Calendar'[Date])
        ),
        KEEPFILTERS(Ftd)
    )
RETURN 
    Result

[ohnothimagain]

Thank you again!
I just copied that you wrote as "Ftd_sum2" ,but unfortunately it was unsuccess..(blank values)
can you think of anything that might have caused it??

Best regards,

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[ohnothimagain]

Thank you for your reply! but  I'm afraid to say that  I'm not in usable condition so far.

Anyway,once again I really appreciate your support.