Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Heena_9980400
Helper III
Helper III

Trying to get 3 conditions, last 15 days, last 30 days and other than last 30 days DAX

‎10-21-2024 11:33 PM

Hi Team,

Hope you all are doing fine!

 

Actually was trying to crreate 3 buttons like last 15 days,last 30 days and other than last 30 days.
I tried achieving this by using below dax but for 30 days condition its giving only last 15 days data.

 

In the last=
IF (
    DATEDIFF ( tij2xgen5_formatted_pen_pedigree_dataset[penetestts], TODAY(), DAY ) <= 15,
    "Last 15 days",
    IF (
        DATEDIFF ( tij2xgen5_formatted_pen_pedigree_dataset[penetestts], TODAY(), DAY ) > 15
        && DATEDIFF ( tij2xgen5_formatted_pen_pedigree_dataset[penetestts], TODAY(), DAY ) <= 30,
        "Last 30 days",
        "Other than last 30 days"
    )
)

when i try without last 15 days conditions, it works fine but for above dax its not giving correct data.

looking for your help.Thanks in advance.
 
 
 
Regards,
Hk
Message 1 of 11
524 Views
0
1 ACCEPTED SOLUTION
Heena_9980400
Helper III
Helper III
In response to Rupak_bi

‎10-22-2024 04:49 AM

You mean , in the DAX i need to change month name to month number or in calculated column.

I did not get it, Could you please help me with the understanding. 

View solution in original post

Message 9 of 11
358 Views
0
10 REPLIES 10
v-nuoc-msft
Community Support
Community Support

‎10-24-2024 02:07 AM

Hi @Heena_9980400 

 

Thank you very much Rupak_bi and DanielW_ for your prompt reply.

 

Can you tell me if your problem is solved? If yes, please accept it as solution.

 

Regards,

Nono Chen

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

Message 11 of 11
247 Views
0
Rupak_bi
Post Prodigy
Post Prodigy

‎10-22-2024 03:40 AM

its working

Rupak_bi_0-1729593452189.png

Rupak_bi_1-1729593503600.png

Make sure you are you are converting month name to month number befor using datediff.

 

If this solves your topic, please accept as solution.



Regards
Rupak
FOLLOW ME : https://www.linkedin.com/in/rupaksar/
Message 8 of 11
397 Views
Heena_9980400
Helper III
Helper III
In response to Rupak_bi

‎10-22-2024 04:49 AM

You mean , in the DAX i need to change month name to month number or in calculated column.

I did not get it, Could you please help me with the understanding. 

Message 9 of 11
359 Views
0
Rupak_bi
Post Prodigy
Post Prodigy
In response to Heena_9980400

‎10-22-2024 06:34 AM

hi @Heena_9980400 

See, your month column is in text format and "datediff" will work on date format only. So you need to transform month name to  month number. Please refer the first variable in my DAX where I have a switch function converting month name to month number. I did it only for two months September and October . this you need to edit for all the months. thats it. your problem will be solved.
I have created a calculated column as a solution, you may use the same dax to create a measure if required. 

thanks.

 

If this solves your problem, please accept as solution.



Regards
Rupak
FOLLOW ME : https://www.linkedin.com/in/rupaksar/
Message 10 of 11
348 Views
Heena_9980400
Helper III
Helper III

‎10-22-2024 02:29 AM
YearMonthDayproductfamilyCountAQMQS1S2S3 Line
2024October1xxxxx1453100.00%0.00%100.00%0.00%0.00%zzzzz
2024October1yyyyy35100.00%100.00%100.00%0.00%0.00%xxxxx
2024October1yyyyy206100.00%99.51%100.00%0.00%0.00%xxxxx
2024October1xxxxx6838100.00%94.79%100.00%0.00%0.00%xxxxx
2024October1zzzzz1705100.00%100.00%100.00%100.00%100.00%yyyyy
2024October2ttttt424100.00%0.00%100.00%0.00%0.00%zzzzz
2024October2xxxxx576100.00%0.00%100.00%0.00%0.00%zzzzz
2024October2xxxxx3292100.00%99.64%100.00%0.00%0.00%xxxxx
2024October2zzzzz775899.94%99.73%100.00%100.00%100.00%yyyyy
2024October3xxxxx809100.00%84.67%100.00%0.00%0.00%zzzzz
2024October3xxxxx13083100.00%86.07%100.00%0.00%0.00%xxxxx
2024October3zzzzz2025899.80%99.80%99.99%99.92%99.89%yyyyy
2024October4ttttt2100.00%0.00%100.00%0.00%0.00%zzzzz
2024October4yyyyy3100.00%100.00%100.00%0.00%0.00%zzzzz
2024October4gggg6078100.00%89.72%100.00%0.00%0.00%xxxxx
2024October4gggg89100.00%100.00%100.00%0.00%0.00%xxxxx
2024October4zzzzz3100.00%100.00%100.00%100.00%100.00%zzzzz
2024October4zzzzz14808100.00%99.97%100.00%100.00%100.00%yyyyy
2024October5hhhhhhhh6713100.00%86.30%100.00%0.00%0.00%xxxxx
2024October5zzzzz1650999.98%99.27%100.00%100.00%100.00%yyyyy
2024October6hhhhhhhh13844100.00%85.96%100.00%0.00%0.00%xxxxx
2024October7hhhhhhhh2073100.00%100.00%100.00%0.00%0.00%zzzzz
2024October7hhhhhhhh14165100.00%85.54%100.00%0.00%0.00%xxxxx
2024October7hhhhhhhh1100.00%100.00%100.00%0.00%0.00%xxxxx
2024October7gggg102100.00%100.00%100.00%0.00%0.00%xxxxx
2024October7gggg57496.86%96.86%96.86%0.00%0.00%xxxxx
2024October7zzzzz1397499.99%99.98%100.00%99.99%99.99%yyyyy
2024October8hhhhhhhh2247100.00%100.00%100.00%0.00%0.00%zzzzz
2024October8hhhhhhhh16111100.00%85.92%100.00%0.00%0.00%xxxxx
2024October8gggg96100.00%100.00%100.00%0.00%0.00%xxxxx
2024October8gggg8369.88%69.88%100.00%0.00%0.00%xxxxx
2024October8zzzzz1265299.95%99.94%100.00%100.00%100.00%yyyyy
2024October9hhhhhhhh10391100.00%85.80%100.00%0.00%0.00%xxxxx
2024October9zzzzz12118100.00%99.98%100.00%100.00%100.00%yyyyy
2024October10hhhhhhhh20410100.00%86.07%100.00%0.00%0.00%xxxxx
2024October10gggg94100.00%100.00%100.00%0.00%0.00%xxxxx
2024October10gggg29100.00%100.00%100.00%0.00%0.00%xxxxx
2024October10zzzzz217699.95%99.95%100.00%100.00%100.00%yyyyy
2024October11hhhhhhhh14951100.00%86.01%100.00%0.00%0.00%xxxxx
2024October11gggg80100.00%100.00%100.00%0.00%0.00%xxxxx
2024October11gggg65896.35%96.35%100.00%0.00%0.00%xxxxx
2024October11zzzzz24607100.00%99.94%100.00%100.00%100.00%yyyyy
2024October12hhhhhhhh1661799.99%86.01%100.00%0.00%0.00%xxxxx
2024October12zzzzz1654699.99%99.94%100.00%100.00%100.00%yyyyy
2024October13hhhhhhhh1386099.99%85.53%100.00%0.00%0.00%xxxxx
2024October13CHAMELEON LG297100.00%100.00%100.00%100.00%100.00%yyyyy
2024October14hhhhhhhh19164100.00%91.85%100.00%0.00%0.00%xxxxx
2024October14CHAMELEON LG11698100.00%100.00%100.00%100.00%100.00%yyyyy
2024October14CURIE LG4897.92%0.00%100.00%100.00%100.00%zzzzz
2024October15hhhhhhhh21866100.00%97.17%100.00%0.00%0.00%xxxxx
2024October15CHAMELEON LG17645100.00%99.98%100.00%100.00%100.00%yyyyy
2024October15CURIE LG758100.00%0.00%100.00%100.00%100.00%zzzzz
2024October16hhhhhhhh5410100.00%97.01%100.00%0.00%0.00%xxxxx
2024October16CASSINI LG50100.00%0.00%100.00%100.00%100.00%zzzzz
2024October16CHAMELEON LG14525100.00%99.99%100.00%100.00%100.00%yyyyy
2024October16CURIE LG1006100.00%0.00%100.00%100.00%100.00%zzzzz
2024October17hhhhhhhh16483100.00%87.74%100.00%0.00%0.00%xxxxx
2024October17CASSINI LG390100.00%0.00%100.00%100.00%100.00%zzzzz
2024October17CHAMELEON LG8944100.00%99.99%100.00%100.00%100.00%yyyyy
2024October18hhhhhhhh20817100.00%86.00%100.00%0.00%0.00%xxxxx
2024October18CHAMELEON LG16221100.00%99.98%100.00%100.00%100.00%yyyyy
2024October19hhhhhhhh11854100.00%85.56%100.00%0.00%0.00%xxxxx
2024October19CHAMELEON LG15681100.00%99.97%100.00%100.00%100.00%yyyyy
2024October20hhhhhhhh13865100.00%85.75%100.00%0.00%0.00%xxxxx
2024October20CHAMELEON LG6881100.00%99.99%100.00%100.00%100.00%yyyyy
2024October21hhhhhhhh13400100.00%85.44%100.00%0.00%0.00%xxxxx
2024October21CASSINI LG985100.00%0.00%100.00%100.00%100.00%zzzzz
2024October21CHAMELEON LG11429100.00%99.98%100.00%100.00%100.00%yyyyy
2024October22hhhhhhhh1735100.00%99.42%100.00%0.00%0.00%xxxxx
2024October22CHAMELEON LG124100.00%100.00%100.00%100.00%100.00%yyyyy
2024September1hhhhhhhh156899.11%99.11%100.00%0.00%0.00%xxxxx
Message 5 of 11
440 Views
0
Heena_9980400
Helper III
Helper III
In response to Heena_9980400

‎10-22-2024 02:30 AM

Hi @Rupak_bi ,

 

Please find the sample data above.

Message 6 of 11
439 Views
0
DanielW_
Frequent Visitor
In response to Heena_9980400

‎10-22-2024 03:02 AM

Hi @Heena_9980400 ,
assuming the column [penetestts] is the date combination of [Year], [Month], [Day] in your test data, I believe I was able to created the desired effect witht the folowing dax column:

In the last =
VAR _TestDate = tij2xgen5_formatted_pen_pedigree_dataset[penetestts]
VAR _TodayDate = TODAY()
RETURN
    SWITCH(
        TRUE(),
        DATEDIFF(_TestDate, _TodayDate, DAY) <= 15, "Last 15 days",
        DATEDIFF(_TestDate, _TodayDate, DAY) <= 30, "Last 30 days",
        "Other than last 30 days"
    )
 
Here is an example, counting the rows per category:
DanielW__0-1729591351780.png

The SWITCH function is more efficient and makes it easier to read, compared to multiple nested IF-statements.

Message 7 of 11
417 Views
0
Rupak_bi
Post Prodigy
Post Prodigy

‎10-22-2024 01:20 AM

plz share sample data in plain text here after replacing sensetive data with dummy. 

 



Regards
Rupak
FOLLOW ME : https://www.linkedin.com/in/rupaksar/
Message 4 of 11
478 Views
0
Rupak_bi
Post Prodigy
Post Prodigy

‎10-21-2024 11:50 PM

Hi @Heena_9980400 
your Dax should work. Alternatively you may try below simplified virsion as well. if doesn't works, check the date formats , else share sample data

In the last=
var date_diff = DATEDIFF ( tij2xgen5_formatted_pen_pedigree_dataset[penetestts], TODAY(), DAY )
 
return
switch(true(), date_diff<=15,"Last 15 days",date_diff<=30,"Last 30 days","Other than last 30 days")


Regards
Rupak
FOLLOW ME : https://www.linkedin.com/in/rupaksar/
Message 2 of 11
505 Views
0
Heena_9980400
Helper III
Helper III
In response to Rupak_bi

‎10-22-2024 01:02 AM

Hi @Rupak_bi ,

Thnaks for the response.

still its not giving the correct answer, i checked the date format evrything, its correct.
if i remove "last 15 days ". the last 30 days will correct data.

In the last=
var date_diff = DATEDIFF ( tij2xgen5_formatted_pen_pedigree_dataset[penetestts], TODAY(), DAY )
 
return
switch(true(), date_diff<=30,"Last 30 days","Other than last 30 days").

when i add last 15 days condition, its not wroking for ast 30 days.

Message 3 of 11
482 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All