cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chris_k Regular Visitor
Regular Visitor

Cumulative Total with filters and cut off

Hello,

 

I have sucessfully produced a running total from a pivoted table however I now need to stop the measure producing a flat line at once the data doesnt carry on increasing. See graph with annotation.

 

Table:

IndexAttributeDate
46524Finish17-Feb-17
46527Finish17-Feb-17
42683Finish03-Mar-17
42687Finish03-Mar-17
42692Finish03-Mar-17
5234Finish17-Nov-17
5236Finish17-Nov-17
5238Finish17-Nov-17
45005ActualFinish28-Jul-17
45634ActualFinish28-Jul-17
45635ActualFinish28-Jul-17
45636ActualFinish28-Jul-17
45637ActualFinish28-Jul-17
45428ActualFinish12-Sep-17
45429ActualFinish12-Sep-17

 

Current Graph:

Capture.PNG

Measure Used:

 

Cumu Actual Finish = 
CALCULATE (
    COUNTA ( Asta_FullOutput_Pivoted[Index] ),
    FILTER (
        ALL ( Asta_FullOutput_Pivoted ),
        Asta_FullOutput_Pivoted[Date] <= MAX ( Asta_FullOutput_Pivoted[Date] )
    ),
    FILTER (
        ALL ( Asta_FullOutput_Pivoted ),
        Asta_FullOutput_Pivoted[Attribute]="ActualFinish"
    )
)
5 REPLIES 5
Phil_Seamark Super Contributor
Super Contributor

Re: Cumulative Total with filters and cut off

HI @chris_k

 

You could try wrapping an IF function around it with a test on Date.

 

 

Cumu Actual Finish = 
VAR myVal = 
CALCULATE (
    COUNTA ( Asta_FullOutput_Pivoted[Index] ),
    FILTER (
        ALL ( Asta_FullOutput_Pivoted ),
        Asta_FullOutput_Pivoted[Date] <= MAX ( Asta_FullOutput_Pivoted[Date] )
    ),
    FILTER (
        ALL ( Asta_FullOutput_Pivoted ),
        Asta_FullOutput_Pivoted[Attribute]="ActualFinish"
    )
)

RETURN IF(MAX(Asta_FullOutput_Pivoted[Date]) > MAX('date column from axis'), BLANK() , myVal )

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

chris_k Regular Visitor
Regular Visitor

Re: Cumulative Total with filters and cut off

Unfortunately this give the exact same result...Capture.PNG

v-xjiin-msft Super Contributor
Super Contributor

Re: Cumulative Total with filters and cut off

Hi @chris_k

 

I cannot repro your issue with your shared sample data. Could you please share us your pbix file with One Drive or Dropbox or something else if possible?

 

Thanks,
Xi Jin.

chris_k Regular Visitor
Regular Visitor

Re: Cumulative Total with filters and cut off

Hi,

 

THanks for your help.

 

FIle here: 

 

https://www.dropbox.com/s/mr40fwd4leaarm3/Sample.pbix?dl=0

 

 

pfdickson Visitor
Visitor

Re: Cumulative Total with filters

Hi Everyone,

 

Actually in our organization, operating with multiple projects (Called as Project Codes) under the project Code we are feeding data on amonthly basis with Cost wise, Expense code wise, Funding Codes and Output Codes. Like the below attached screen shot.

Screen Shot.PNG

Screen Shot 2.PNGScreen Shot 3.PNG

 

My question is, How can I filter data period wise and Project wise cumulative amounts under the each section of budget and actual. Kindly advice me support me?

 

The Burn rate is not changing and it is like frozen.

 

Kindly advice and guide me