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.
Hi,
I was wondering whether to calculate number of days between two milestones, for example:
How many days a store took to achieve their first million in sales, and the second million......
I used the following :
First Million = CALCULATE(DISTINCTCOUNT(Sales[Date]),Sales[Amount]<=1000000)
Result seems to be incorrect
Second Million = CALCULATE(DISTINCTCOUNT(Sales[Date]),FILTER(Sales,Sales[Amount]>=1000000 && Sales[Amount]<=2000000)
Result only blank rows
Any suggestions?
Solved! Go to Solution.
Sample file attached as well
Hi shakirkhanani ,
"Result seems to be incorrect"
<--- What's the format of date column and your expected value? And could you share a sample file with some dummy data for further analysis?
Regards,
Jimmy Tao
Thanking for taking interest. Please find below the link to the sample file, I have removed the unnecessary columns.
Are you getting correct result for first million?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
No Sir, its not giving correct answer.
can you share sample dataset for the solution.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Please try this MEASURE
I assume you have data like this
Store Code | Date | Amount |
PK01 | Saturday, January 6, 2018 | 259,322 |
PK01 | Thursday, January 11, 2018 | 199,957 |
PK01 | Thursday, January 25, 2018 | 143,811 |
PK01 | Saturday, February 3, 2018 | 121,347 |
PK01 | Tuesday, February 6, 2018 | 220,141 |
PK01 | Wednesday, February 14, 2018 | 180,620 |
PK01 | Friday, February 16, 2018 | 267,602 |
PK01 | Friday, March 23, 2018 | 174,671 |
PK01 | Saturday, April 7, 2018 | 148,055 |
PK01 | Saturday, April 14, 2018 | 125,927 |
PK01 | Friday, April 20, 2018 | 273,066 |
PK01 | Monday, May 7, 2018 | 154,767 |
PK01 | Sunday, June 3, 2018 | 181,640 |
PK01 | Saturday, June 23, 2018 | 283,012 |
First Million = VAR temp = SUMMARIZE ( Sales, Sales[Date], "Cumulative", VAR mydate = Sales[Date] RETURN CALCULATE ( SUM ( Sales[Amount] ), Sales[Date] <= mydate ) ) VAR DateofAchievemnt = MINX ( FILTER ( temp, [Cumulative] > 1000000 ), [Date] ) RETURN DATEDIFF ( FIRSTDATE ( Sales[Date] ), DateofAchievemnt, DAY )
Thanks for the help!, let me try and report back to you soon.
And heres the secondmillion formula
Second Million = VAR temp = SUMMARIZE ( Sales, Sales[Date], "Cumulative", VAR mydate = Sales[Date] RETURN CALCULATE ( SUM ( Sales[Amount] ), Sales[Date] <= mydate ) ) VAR DateofAchievemnt = MINX ( FILTER ( temp, [Cumulative] > 2000000 ), [Date] ) RETURN DATEDIFF ( FIRSTDATE ( Sales[Date] ), DateofAchievemnt, DAY ) - [First Million]
Sample file attached as well
Awesome!
Your idea is far better than my work around, which was to calculate running total first and then using Calculate and Filter functions to get required millions. Although got the same results, but taking running total on larget dataset, really took a toll on my report and slowed it.
Thanks a million yaar!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
183 | |
111 | |
105 | |
77 | |
70 |