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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Nimai123
Post Patron
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.

 

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

 

Capture.PNG

 

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 

1 ACCEPTED SOLUTION

@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

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

Nimai123
Post Patron
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.

 

Capture.PNG

 

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.

@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

 

amitchandak
Super User
Super User

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

amitchandak
Super User
Super User

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

Ideally, you should date table

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

 

Capture.PNG

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors