Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am needing to report a running total for a distinct count measure by day.
I am currently using the following measure: Running Total = CALCULATE(MyDistinctCount, DATESYTD(Calendar[Date])). But this is yielding the following:
Date | MyDistinctCount | Running Total |
1/1/2020 | 11 | 11 |
1/2/2020 | 11 | 11 |
1/3/2020 | 12 | 12 |
1/4/2020 | 12 | 12 |
1/5/2020 | 11 | 12 |
1/6/2020 | 12 | 13 |
What I am looking for is the following:
Date | MyDistinctCount | Running Total |
1/1/2020 | 11 | 11 |
1/2/2020 | 11 | 22 |
1/3/2020 | 12 | 34 |
1/4/2020 | 12 | 46 |
1/5/2020 | 11 | 57 |
1/6/2020 | 12 | 69 |
Thanks for the help
Solved! Go to Solution.
@Anonymous this is your measure to distinct count over period and not sure if this is what you are looking for
SUMX(FILTER(ALLSELECTED('Calendar'[Date]), 'Calendar'[Date]<= MAX('Calendar'[Date])),CALCULATE(DISTINCTCOUNT(TimeLog[Rig_Name])))
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
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.
@Anonymous what is your distinctcount mesure
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.
@Anonymous
What does the DAX for the measure MyDistinctCount look like?
It is a pretty simple Distinct Count Measure
Rig Days = DISTINCTCOUNT(Schedule[Rig_Name])
In your report you have to pull the date field from your Calendar table and not from the Schedule table. Meaning in the visual itself, the date field you use has to come from the Calendar table.
Strange, the only way I was able to replicate what you are seeing is if I pulled the date from my fact table rather than my calendar table. Would you be able to share your .pbix file? (upload it to OneDrive or DropBox and share the link here).
@Anonymous you are in safe hands with @jdbuchanan71 but one last thing to check befoe you share files with @jdbuchanan71 . Make sure your date dimension is marked as a date table.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
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.
It's interesting, DAX doesn't seem to care if it is marked as a date table. I added another date table to my test model and marked the new one as the date table. The YTD still works pointing at the original date table as long as my date field in the visual and the date field in my measure are from the same table. In the example below the table 'Dates' is not the model's date table.
@jdbuchanan71 let me ask you this, do you have auto date/time turned off?
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.
Yes, I do have it turned off. Marking a table as the date table turns it off in the file automatically correct?
@jdbuchanan71 alright try this,
- turn this back on
- don't mark your table as date table
- and now test the measure with this not marked date table and see if you get the correct result. check below, not marking as date table can have an impact
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.
Interesting, I do get the correct result until I add the second .[Date] to the formula. Like your example
DATESYTD(Dates[Date]) works
DATESYTD(Dates[Date].[Date]) does not.
Isn't the second one the same as doing the following? applying an additional filter to the specific date in the date column.
MyDistinctYTD =
VAR _DateRow = SELECTEDVALUE(Dates[Date])
RETURN
CALCULATE([MyDistinctCount],DATESYTD(Dates[Date]),Dates[Date]=_DateRow)
The above gives me the same result as DATESYTD(Dates[Date].[Date])
@jdbuchanan71 yes that will work and my original reason for all this was to make sure the table is marked as a date table and if that is not causing the issue with the measure.
Sorry I didn't want to hijack this post but it is a good discussion. Cheers!!
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.
@jdbuchanan71 in the timelog field, there is a Cumulative Rig On Measure that is what needs the help
@Anonymous this is your measure to distinct count over period and not sure if this is what you are looking for
SUMX(FILTER(ALLSELECTED('Calendar'[Date]), 'Calendar'[Date]<= MAX('Calendar'[Date])),CALCULATE(DISTINCTCOUNT(TimeLog[Rig_Name])))
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
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.
This is the one! Appreciate it. I am assuming a basic DATEYTD() function won't work with distinct like that because it only returns incremental new distinct counts each date period?
I'm glad @parry2k took a look at your example, I didn't get that you were wanting to add the daily amounts together. DATESYTD would work.
SUMX(DATESYTD('Calendar'[Date]),CALCULATE(DISTINCTCOUNT(TimeLog[Rig_Name])))
But it will reset at the start of the year, not sure if that is what you want.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |