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
Anonymous
Not applicable

Cumulative data and Filter

Hi Everyone!

I want to combine cumulative data and a filter of month in a report so that I could show in a graph or table the sum of amount till the month that I filtered.


I mean, if I filter by May I don´t want to see only May, I want to show January, February and May.

There is any way?

 

Thanks!

 


Captura.PNG

 

 

 

1 ACCEPTED SOLUTION
v-haibl-msft
Employee
Employee

@Anonymous

 

With the slicer visual, you cannot do it now. There is no way to create a dynamic month column/table according to your selection in the slicer.

However, you can try to use the Timeline visual. Just drag the progress bar to include January to May.

 

I create a calendar table and a cumulative measure with following formulas. And create relationship between Calendar and Table1 with Date key.

Calendar =
CALENDAR ( "1/1/2016", "12/31/2016" )
Cumulative = 
IF (
    SUM ( Table1[Number] ) <> BLANK (),
    CALCULATE (
        SUM ( Table1[Number] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
    )
)

Cumulative data and Filter_1.jpgCumulative data and Filter_2.jpg

 

Best Regards,

Herbert

View solution in original post

2 REPLIES 2
v-haibl-msft
Employee
Employee

@Anonymous

 

With the slicer visual, you cannot do it now. There is no way to create a dynamic month column/table according to your selection in the slicer.

However, you can try to use the Timeline visual. Just drag the progress bar to include January to May.

 

I create a calendar table and a cumulative measure with following formulas. And create relationship between Calendar and Table1 with Date key.

Calendar =
CALENDAR ( "1/1/2016", "12/31/2016" )
Cumulative = 
IF (
    SUM ( Table1[Number] ) <> BLANK (),
    CALCULATE (
        SUM ( Table1[Number] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
    )
)

Cumulative data and Filter_1.jpgCumulative data and Filter_2.jpg

 

Best Regards,

Herbert

Anonymous
Not applicable

Thanks @v-haibl-msft

 

It isn't the solution that I expect but It's an amazing alternative.

 

Thanks!

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.