Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Dear Folks,
I have two tables and relationship as shown below.
1. Financials
2. DateTable
I have created a slicer with the Date table and also created a measure in Financial table for cumulative sales as shown below.
When I choose the date in slicer as "Feb2014" month (for example), am NOT getting the cumulative sales (rather am getting the respective sales for each month) . What I actually need is the cumulative sales starting from the beginning (i.e if I bring the date field from Financial table as one of the column, I need the cumulative sales alongside each date until the date I choose from Slicer).
Can someone PLEASE help me how to achieve the cumulative sales for each month (until the month I choose from Slicer)?
Kindly let me know if you need any information from my side.
I can provide my PBIX file if required.
Thank you.
Current Output:
------------------
Regards
Kumar
Solved! Go to Solution.
Let's slightly modify the expression of the measure, pls try again.
Cumulative Sales =
VAR SlicerDate = MAX(DateTable[Date])
VAR RowDate = CALCULATE(MAX(Financials[Date]),ALL(DateTable))
RETURN
CALCULATE(
IF(NOT ISEMPTY(financials),
CALCULATE(
SUM(financials[ Sales]),
FILTER(ALLEXCEPT(Financials,DateTable),Financials[Date] <= RowDate )
)
),
FILTER(ALL(DateTable),DateTable[Date] <= SlicerDate )
)
Hi,
Try this measure:
Cumulative Sales = CALCULATE(SUM(financials[ Sales]),DATESBETWEEN(DateTable[Date],MINX(ALL(dateTable),DateTable[Date]),MAX(DateTable[Date])))
Hi @Ashish_Mathur ,
Thanks for your response. Have tried you using the measure as you suggested. It gives me the sum for the respective year as shown below (please refer to the measure - Cumulative Sales 4). Kindly note that I have applied xifeng solution and got the result (pls refer to the measure - Cumulative Sales 3).
I canno do much with a screenshot. Show the expected result very clearly. Share the download link of the PBI file.
Hi @kskumar ,
You can try below measure:
Cumulative Sales =
VAR SlicerDate = MAX(DateTable[Date])
VAR RowDate = CALCULATE(MAX(Financials[Date]),ALL(DateTable))
RETURN
CALCULATE(
IF(NOT ISEMPTY(financials),
CALCULATE(
SUM(financials[ Sales]),
FILTER(ALL(Financials[Date]),Financials[Date] <= RowDate )
)
),
FILTER(ALL(DateTable),DateTable[Date] <= SlicerDate )
)
Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
Thank you very much @xifeng_L for your quick response.
This solution works perfectly showing the Cumulative sales. However, If I bring the year into the column list, it did not show as expected. I also have a column "Fiscal Year" in table "Financials", as I need to show the "sales" financial year wise (in my case, financial year is from Jul to Jun). so If I bring "Fiscal year" or "Year" into the column list, it shows the values only for the respective year (not as running sales total). If you see the below screenshot, I see the sales for respective Fiscal year (instead of running total).
Can you please advise me how to achieve the running total - fiscal year wise.
Thanks again for your help. Please let me know if you need my PBIX file/ any other information you may require.
Current output:
Sample Data from "financial" table
Let's slightly modify the expression of the measure, pls try again.
Cumulative Sales =
VAR SlicerDate = MAX(DateTable[Date])
VAR RowDate = CALCULATE(MAX(Financials[Date]),ALL(DateTable))
RETURN
CALCULATE(
IF(NOT ISEMPTY(financials),
CALCULATE(
SUM(financials[ Sales]),
FILTER(ALLEXCEPT(Financials,DateTable),Financials[Date] <= RowDate )
)
),
FILTER(ALL(DateTable),DateTable[Date] <= SlicerDate )
)
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
80 | |
61 | |
60 | |
58 |
User | Count |
---|---|
155 | |
119 | |
103 | |
77 | |
71 |