Forum Discussion
Time Intelligence not working for me
There must be someting I'm missing here. I can manually do the following to get YTD and it works on my data. That is it resets the total for each year etc. I would like to do a "TOTALYTD" version but it won't work. Note the 'Date' table has all dates and from what I can see has been "automatically" made to be the calendar for the model.
Alarms YTD =
CALCULATE(
COUNTROWS(system),
FILTER (
ALL(system),
AND(
system[Time].[Date] >= DATE( YEAR ( MAX ( 'Date'[Calendar].[Date] ) ), 1, 1),
system[Time].[Date] <= MAX ('Date'[Calendar].[Date])
)
)
)But for quartely this one below does not work. It does not reset the the count each quarter.
Alarms QTD =
CALCULATE(
COUNTROWS(system),
FILTER (
ALL(system),
AND(
system[Time].[Date] >= STARTOFQUARTER('Date'[Calendar].[Date]),
system[Time].[Date] <= MAX ('Date'[Calendar].[Date])
)
)
)Here's a screenshot of the matrix results.
I've tried all kinds of Time Intelligence ways to get it but it won't work. Note that my data is counting the number of rows not a mathematical total as I am counting "events".
Finally, if I can get this to work with the time intelligence then I need to get it all to work to shoe the current quarter and year total to today on a card.
Thanks all in advance!
7 Replies
- Daniil
Kudo Kingpin
Have you tried the following way?
Alarms YTD = TOTALYTD ( COUNTROWS ( system ), 'Date'[Calendar] )
Alarms QTD = TOTALQTD ( COUNTROWS ( system ), 'Date'[Calendar] )
- Ashish_Mathur
Super User
Hi,
Share the link from where i can download your file. PLease also show the expected result there.
- v-jiascu-msft
Microsoft Employee
Hi misterian,
Where are the dates in the screenshot from? It would be great if you can provide a sample. Please mask the confidential parts first.
Best Regards,
Dale
- misterianRegular Visitor
Thanks for the help here. Here is a link to the files on Onedrive.
I've also done some more messign around and I think COUNTA may be the best way to count the events as it works on a columnar basis, but it does not slice or filter, e.g. by site.
- Ashish_Mathur
Super User