cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors