Cumulative Daily Sum of a Distinct Count Item

[Anonymous]

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

[parry2k]

@Anonymous what is your distinctcount mesure

[jdbuchanan71]

@Anonymous 

What does the DAX for the measure MyDistinctCount look like?

[Anonymous]

It is a pretty simple Distinct Count Measure 

Rig Days = DISTINCTCOUNT(Schedule[Rig_Name])

[jdbuchanan71]

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.

[Anonymous]

@jdbuchanan71 it is though

[jdbuchanan71]

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).

[parry2k]

@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.⚡

[jdbuchanan71]

@parry2k 

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.

[parry2k]

@jdbuchanan71 let me ask you this, do you have auto date/time turned off?

[jdbuchanan71]

Yes, I do have it turned off.  Marking a table as the date table turns it off in the file automatically correct?

[parry2k]

@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

[jdbuchanan71]

@parry2k 

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])

[parry2k]

@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!!

[Anonymous]

@jdbuchanan71  here is a github repo with the Weekly Report project

https://github.com/rstover/PowerBI

[Anonymous]

@jdbuchanan71  in the timelog field, there is a Cumulative Rig On Measure that is what needs the help

[parry2k · Accepted 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.⚡

[Anonymous]

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?

[jdbuchanan71]

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.