Time Intelligence not working for me

[misterian]

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!

[v-jiascu-msft]

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

[misterian]

Thanks for the help here. Here is a link to the files on Onedrive.

https://betatechinc-my.sharepoint.com/personal/butterworthi_btieng_com/_layouts/15/guestaccess.aspx?...

https://betatechinc-my.sharepoint.com/personal/butterworthi_btieng_com/_layouts/15/guestaccess.aspx?...

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 · Accepted solution]

Hi @misterian,

You may refer to my solution in this file.

[misterian]

Thanks, this gets me moving forward. I wish DAX wasn't so fussy.

For example, for the calculated table formula. You used the successful

Date = CALENDAR(MIN(system[AlarmTime]),MAX(system[AlarmTime]))

And I had the not working following which DAX likes to put in when I type in the expression.

Date = CALENDAR(MIN(system[AlarmTime].[Date]),MAX(system[AlarmTime].[Date]))

Also I see the relationships are critical from the date table to my date fields. I note that the relationship is between a date time column and a date column. I guess DAX figures this out.

Thanks

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.  PLease also show the expected result there.

[Daniil]

Have you tried the following way?

Alarms YTD = TOTALYTD ( COUNTROWS ( system ), 'Date'[Calendar] )

Alarms QTD = TOTALQTD ( COUNTROWS ( system ), 'Date'[Calendar] )