Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
misterian
Regular Visitor

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.

table.JPG

 

 

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!

1 ACCEPTED SOLUTION

Hi @misterian,

 

You may refer to my solution in this file.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
v-jiascu-msft
Employee
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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Hi @misterian,

 

You may refer to my solution in this file.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Daniil
Kudo Kingpin
Kudo Kingpin

Have you tried the following way?

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.