Forum Discussion
Cumulative Counts
HI Anonymous ,
You can download my proposed solution from here.
Here is the DAX formula I propose for it:
Cumulative_Count 2 = var currentDate = MAX('Reservations'[res_created_at_date]) return CALCULATE(COUNTROWS(FILTER('Reservations','Reservations'[res_created_at_date]<=currentDate)), ALL('Reservations'[res_created_at_date].[Month], 'Reservations'[res_created_at_date].[MonthNo], 'Reservations'[res_created_at_date].[Day]))Let me know if that works for you.
LC
Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com
HI Anonymous ,
You can download my proposed solution from here.
Here is the DAX formula I propose for it:
Cumulative_Count 2 =
var currentDate = MAX('Reservations'[res_created_at_date])
return CALCULATE(COUNTROWS(FILTER('Reservations','Reservations'[res_created_at_date]<=currentDate)), ALL('Reservations'[res_created_at_date].[Month], 'Reservations'[res_created_at_date].[MonthNo], 'Reservations'[res_created_at_date].[Day]))
Let me know if that works for you.
LC
Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com
Thank you lc_finance,
Yes, that formula does appear to work. Can you expand on why you used the [.Month], [.Day] tags after the columns name. Normally I only need to name the column and do not need to specify those items. Could this be why my graph does not act like other I have used? Normally I can see everyday when I move my mouse over a graph and click on the specific day to drill down. The chart in your example appears to only be able to do months.
Thank you for the continued help.
Hi Anonymous ,
The need for specifying [.Month], [.Day] is because Power BI 'derives' multiple columns from a Date column.
These 'derived' columns are Month, Day, Year, Quarter, etc. These additional columns allow you to filter by days, month, quarter, etc when you have a Date column.
Now back to your request: as you want to show all reservations made prior to a certain date, I had to remove the filter on Dates.
If I did not remove the filter on Dates, you would see on June 15 only reservation made on June 15, on June 16 only reservation made on the 16, etc. I removed them using ALL, and I had to apply ALL to the different 'derived' columns as above.
Does this help you?
LC
Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com
Thanks lc_finance. Yes that helps. I did not know that about the column treatment for dates. So how would go about having a graph like the below which allows me to mouse over specific calender days to see the metrics. The x-axis still shows months and years, but I can also mouse over specific days. My current graph works different. I can only have month, quarters, days, but not all at once, which causes the graph to not be very flexible.
Thank you again for your help.