Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

DAX-formula challenge: 3 different formulas but not the right one. Who helps?

Hello everyone,

 

I'm struggling with a DAX-formula. I have 3 different formulas but not a right one.

 

The context is year-month en then 2004-2. I want a CALCULATETABLE for the period 01-02-2003 until 31-01-2004 but a dynamic one. The right answer should be 356 but all the answers from the 3 formulas are 384.

 

The last day of 2004-2 is 29-02-2004 (leap year). Could that be the reason? I don't know, can please someone help me?

 

Thanks in advance,

 

Cor

 

@Zubair_Muhammad

2004-02 Challenge.JPG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

The right answer is:

 

Won Clients LTM =
VAR CustomerLTM = CALCULATETABLE(VALUES('Invoices and Categories'[customer]);
                    DATESBETWEEN ('Calendar'[Date];
                         NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) );
                         LASTDATE ( 'Calendar'[Date] )))
VAR PriorCustomers = CALCULATETABLE(VALUES('Invoices and Categories'[customer]);
                       DATESBETWEEN('Calendar'[Date];
                         DATEADD(STARTOFMONTH('Calendar'[Date]);-12;MONTH);
                         LASTDATE(DATEADD('Calendar'[Date];-1;MONTH))))            
RETURN
COUNTROWS(
    EXCEPT(CustomerLTM;PriorCustomers))

 

Thanks everyone! 🙂

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

Are you using a standard calendar table? Can you paste your formulas as text?

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi Greg,

 

Yes, I'm using a Calendar Table.

 

The measures:

Won Clients LTM =
VAR CustomerLTM = CALCULATETABLE(VALUES('YH Invoices'[Customer]);
                    DATESBETWEEN ('Calendar'[Date];
                         NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) );
                         LASTDATE ( 'Calendar'[Date] )))
VAR PriorCustomers = CALCULATETABLE(VALUES('YH Invoices'[Customer]);
                       DATESBETWEEN('Calendar'[Date];
                        DATEADD(STARTOFMONTH('Calendar'[Date]);-12;MONTH);
                        DATEADD(ENDOFMONTH('Calendar'[Date]);-1;MONTH)))             
RETURN
COUNTROWS(
    EXCEPT(CustomerLTM;PriorCustomers))

 

Won2 Clients LTM =
VAR CustomerLTM = CALCULATETABLE(VALUES('YH Invoices'[Customer]);
                    DATESBETWEEN ('Calendar'[Date];
                         NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) );
                         LASTDATE ( 'Calendar'[Date] )))
VAR PriorCustomers = CALCULATETABLE(VALUES('YH Invoices'[Customer]);
                       DATESBETWEEN('Calendar'[Date];
                         NEXTDAY ( SAMEPERIODLASTYEAR ( DATEADD(LASTDATE('Calendar'[Date]);-1;MONTH)));
                         DATEADD(LASTDATE('Calendar'[Date]);-1;MONTH)))
RETURN
COUNTROWS(
    EXCEPT(CustomerLTM;PriorCustomers))

 

Won3 Clients LTM =
VAR CustomerLTM = CALCULATETABLE(VALUES('YH Invoices'[Customer]);
                    DATESBETWEEN ('Calendar'[Date];
                         NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) );
                         LASTDATE ( 'Calendar'[Date] )))
VAR PriorCustomers = CALCULATETABLE(VALUES('YH Invoices'[Customer]);
                       DATESBETWEEN('Calendar'[Date];
                         DATEADD(STARTOFMONTH('Calendar'[Date]);-13;MONTH);
                         DATEADD(LASTDATE('Calendar'[Date]);-1;MONTH)))
RETURN
COUNTROWS(
    EXCEPT(CustomerLTM;PriorCustomers))

 

Won4 Clients LTM =
VAR CustomerLTM = CALCULATETABLE(VALUES('YH Invoices'[Customer]);
                    DATESBETWEEN ('Calendar'[Date];
                         NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) );
                         LASTDATE ( 'Calendar'[Date] )))
VAR PriorCustomers = CALCULATETABLE(VALUES('YH Invoices'[Customer]);
                       DATESBETWEEN('Calendar'[Date];
                         DATE(2003;02;01);DATE(2004;01;31)))
RETURN
COUNTROWS(
    EXCEPT(CustomerLTM;PriorCustomers))

 

With kind regards,


Cor

 

Hi @Anonymous,

Could you please offer me some sample data or share the pbix file if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-danhe-msft, can you give me your e-mail? I'll send you the pbix-file.

Hi @Anonymous,

Could you please upload your report to your OneDrive or Dropbox and send the link if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

The right answer is:

 

Won Clients LTM =
VAR CustomerLTM = CALCULATETABLE(VALUES('Invoices and Categories'[customer]);
                    DATESBETWEEN ('Calendar'[Date];
                         NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) );
                         LASTDATE ( 'Calendar'[Date] )))
VAR PriorCustomers = CALCULATETABLE(VALUES('Invoices and Categories'[customer]);
                       DATESBETWEEN('Calendar'[Date];
                         DATEADD(STARTOFMONTH('Calendar'[Date]);-12;MONTH);
                         LASTDATE(DATEADD('Calendar'[Date];-1;MONTH))))            
RETURN
COUNTROWS(
    EXCEPT(CustomerLTM;PriorCustomers))

 

Thanks everyone! 🙂

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors