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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
visubabu24
Frequent Visitor

Dynamic running totals based on Financial year filter selection

Hi All,
I am working on a requirement where if a financial year is selected the Values less than equal to selected FY should sum up for all distinct categories .

For Example :

RowNoCategoryDateFinancial YearAmount
1Furnitures10/10/20232023-24500
2Decors10/09/20222022-23400
3Furnitures10/05/20212021-22300
4Paints12/06/20202020-21200
5Decors10/05/20202020-21100

 

If FY 2023-24 is selected in the filter the categories with latest dates should get aggregated 
in this case row no 1,2,4 should be aggregated and if FY 2022-23 is selected row no 2,3,4 should be aggregated . 
Though I am using SQL source I am restricted to use parameters and stored procs. 
I am Kind of run out of ideas . Your help is appreciated 
Thanks in Advance 

1 ACCEPTED SOLUTION

OK, now I got it

 

lbendlin_0-1699235556419.png

 

see attached. 

 

View solution in original post

7 REPLIES 7
visubabu24
Frequent Visitor

Here the blocker for me is in running total we should consider only the category of latest FY. 

  • for example If I am selecting 2023-24, Furniture category is already there in 2023-24 hence our calculation should ignore 2021-22, decors category already available in 2021_2022 hence calculation should  ignore decors in fy 2021-22

OK, now I got it

 

lbendlin_0-1699235556419.png

 

see attached. 

 

Thank You So much Ibendlin . 

lbendlin
Super User
Super User

Do you mean running totals or simple sums?  What would the expected outcome look like for your two scenarios?

  1. HI lbendlin , it's only the running total  . If FY 2023-24 is selected in filter I should get sum of row nos. 1,2 and 4 so the total is 1100.
  2. If the FY 2022-23 is selected in the filter row nos 2,3,4 needs to sum up hence the total is 900

You get that for free in the UI, no code required. Give it a try.

I tried DAX and I am able to get the running total for all FYs but I am unable to filter out the condition which I have mentioned.  

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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