topic Re: Calculate the count of customers for last 30 days! in DAX Commands and Tips

Calculate the count of customers for last 30 days!

Nimai123 — Thu, 19 Nov 2020 09:09:21 GMT

I tried to use the below DAX but got an error because my Date column was not continious, so i created a date Table and joined with my non-continious date column.

Last 30 Days = CALCULATE(COUNT(tbl_logs[userprincipalname]),DATEADD('Table'[Date],-30,DAY))

And then i used a different measure with the same logic and the asnwer was differet, where did i go wrong ( got correct for few users)

Measure = CALCULATE(COUNT(tbl_logs[userprincipalname]),FILTER(ALL('Table'),'Table'[Date] >= TODAY()-30))

Is there a better way to calculate the count for last 30 days for an uncontinious date column.?

amitchandak

Re: Calculate the count of customers for last 30 days!

amitchandak — Thu, 19 Nov 2020 09:20:41 GMT

Nimai123 , Table is date table with all continuous dates?

Ideally, you should date table

Re: Calculate the count of customers for last 30 days!

Nimai123 — Thu, 19 Nov 2020 09:43:15 GMT

amitchandak

Yes the table is a date table that i have created and the Measure = CALCULATE(COUNT(tbl_logs[userprincipalname]),FILTER(ALL('Table'),'Table'[Date] >= TODAY()-30)) is working properly, but why the Last 30 days Measure is not working as desired dispite of the same logic used.

Can we calculate without using the Date table but just the un-continious date column?

Re: Calculate the count of customers for last 30 days!

amitchandak — Thu, 19 Nov 2020 10:13:39 GMT

Nimai123 , You are not taking any date on the page or in a visual form where dateadd will get the date?

Re: Calculate the count of customers for last 30 days!

Nimai123 — Thu, 19 Nov 2020 14:37:20 GMT

Hello amitchandak

I want to calculate the top 5 count of the users for the last 15 days, 30 days, 45 days, 60 days,... and display it in a graph, i am able to show all the details together.

But not albe to show the top 5 for the last 15 days, 30 days, 45 days and 60 days.

When i create a switch statement, i get the answers right but it doesnt sums up logically, as when i select 30 days it gives me the value without taking the sum of 15 days.

Re: Calculate the count of customers for last 30 days!

Anonymous — Thu, 19 Nov 2020 16:12:43 GMT

Nimai123

It's NOT the same logic by any means.

DATEADD moves the given set of dates by a specified interval.

To get the same logic you have to use DATESINPERIOD with the correct arguments. (Just to stress this point as well: Time-intel functions work correctly ONLY with proper date tables. You can't use them on columns of fact tables).

Re: Calculate the count of customers for last 30 days!

amitchandak — Fri, 20 Nov 2020 02:38:43 GMT

Nimai123 , For this bucketing you need to create a column in you table by subracting from today

Diff = datediff([Date], today(),Day)

Now create a bucket

Switch (True()

[Diff] <15 , "Last 15 Days ",

[Diff] <30 , "Last 15 to 30 Day Days "s

//Add others ,

)

In case you need 30days to overlap 15 days, then you need to create measures