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

[Anonymous]

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

[Greg_Deckler]

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

[Anonymous]

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

[v-danhe-msft]

Hi @Anonymous,

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

Regards,

Daniel He

[Anonymous]

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

[v-danhe-msft]

Hi @Anonymous,

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

Regards,

Daniel He

[Anonymous · Accepted solution]

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! 🙂