how to get data for records taht are more than

[praveenreddykon]

I'm new to powerBI so please forgive me if this question of mine is silly to ask.

I created a measure with the following which kind of returns the data incorrect.

IF( DATEDIFF(AllDevices[LAST_INVENTORY], TODAY(), DAY) <= 30, "30 days",
IF(DATEDIFF(AllDevices[LAST_INVENTORY], TODAY(), DAY) < 60, "60 days",
IF(DATEDIFF(AllDevices[LAST_INVENTORY], TODAY(), DAY) < 90, "90 days", "above 90days"
)))

by my sql statement on the appliance gives it correct.
Select FORMAT(COUNT(m.ID), 'S') as 'Total Devices',
FORMAT(COUNT(CASE
WHEN LAST_INVENTORY <= now() - INTERVAL 30 day
THEN 1
END), 'S') AS 'Missed from 30days',
FORMAT(COUNT(CASE
WHEN LAST_INVENTORY < now() - INTERVAL 60 day
THEN 1
END), 'S') AS 'Missed from 60days',
FORMAT(COUNT(CASE
WHEN LAST_INVENTORY < now() - INTERVAL 90 day
THEN 1
END), 'S') AS 'Missed from 90days'
FROM MACHINE m

Am I doing anything wrong?

Faster answer is much appreciated. 😛

[v-yetao1-msft · Accepted solution]

Hi @praveenreddykon 

The dax formula you provided is a bit problematic in notation .I make some changes .

Measure = IF( DATEDIFF(SELECTEDVALUE(AllDevices[LAST_INVENTORY]), TODAY(), DAY) <= 30, "30 days",
          IF(DATEDIFF(SELECTEDVALUE(AllDevices[LAST_INVENTORY]), TODAY(), DAY) < 60, "60 days",
          IF(DATEDIFF(SELECTEDVALUE(AllDevices[LAST_INVENTORY]), TODAY(), DAY) < 90, "90 days", "above 90days"
)))

And the result is as shown :

Best Regards

Community Support Team _ Ailsa Tao

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

[praveenreddykon]

Hi @Samarth_18 

Thank you for your response. 

I cannot provide you sample data. 

Insead, I can try to explain in details that what I'm doing. 

from the sql query I get the data ready in a table like below:

Since I want to visualize it in a cards in powerBI, I'm trying to created a column MissingDeviceSince that can populate with 30days/60days/90days per the date column reference. that gives me this data:

Missing 30Days: 9800+
Missing 60Days: 5000+

Missing 90days: 4000+

So, if you notice, the numbers does not match. 
But, my question is does the DAX query I have mentioned above looks correct to you?

[Samarth_18]

Hi @praveenreddykon 

Dax code looks fine.

Hope you have created your card visual like below.

Taking count of your new column and add that column again as filter and select the required days.

And more thing to check, if you are using import mode then make sure your Power BI report is having latest data as compare to SQL.

Thanks,

Samarth

[praveenreddykon]

@Samarth_18,
Yes, I used the cards and visualization is fine except the numbers does not match though.
Also yes, import mode with latest data.

[Samarth_18]

Hi @praveenreddykon 

It would be helpful if you could share some sample data with expected output and what output/errors you are getting?

You can refer below article to get your answer quickly 🙂

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071... 

Thanks,

Samarth