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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chris_k
Helper I
Helper I

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"
    )
)
7 REPLIES 7
pfdickson
Regular Visitor

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

 

 

Phil_Seamark
Employee
Employee

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!

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

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.

Hi,

 

THanks for your help.

 

FIle here: 

 

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

 

 

Did you figure this out? I have exactly the same challenge!

Hi

For the following formula:

Cummulative =
CALCULATE (
sum (table[column]),
FILTER (ALL (DateTable[Date]),DateTable[Date] <= MAX ( DateTable[Date]))
)

I found that simply adding another filter would create the desired cut-off. Now my formula looks like this:
DeploymentCummulative =
CALCULATE (
sum (table[column]),
FILTER (ALL (DateTable[Date]),DateTable[Date] <= MAX ( DateTable[Date])),
FILTER (ALL (DateTable[Date]), DateTable[Date]>date(2018,01,01))
)
 
For me it was a question about removing very, very old cases that would basically create a long tail to the important data. It can also work to create a max date cutoff point.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.