cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

## Calculate the count of customers for last 30 days!

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.

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.?

1 ACCEPTED SOLUTION
Super User

@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

)

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

6 REPLIES 6
Anonymous
Not applicable

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).

Post Patron

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.

Super User

@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

)

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

Super User

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

Super User

@Nimai123 , Table is date table with all continuous dates?

Ideally, you should date table

Post Patron

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors