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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

DAX help with Dateadd and TODAY()-1 for MoM calculation

Hello,

 

I'm trying to create a calculation that finds Month over Month % as:  (Current MoM - Prior MoM) / Prior MoM

 

Current MoM calculation is: CALCULATE(SUM(factSales[QuantityShipped]),filter('Date','Date'[Date]=today()-1 )) which gives me the Quantity Shipped amount for Nov 19.

 

I need Prior MoM to be the Quantity Shipped amount for Oct 19, so I was wanting the filter to be something like filter('Date','Date'[Date]=dateadd(today()-1,-1,MONTH ))  but that has an issue with today()

 

I also need to account for end of month dates not matching up, but that can be step 2.

 

 

How can I fix the Prior MoM calculation?

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @Anonymous,

 

The DATEADD function only accepts one column as parameter try the following measure I have already made it to work with change of year.

 

PMOM =
VAR Date_select =
    IF (
        MONTH ( TODAY () ) = 1;
        DATE ( YEAR ( TODAY () ) - 1; 12; DAY ( TODAY () - 1 ) );
        DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ) - 1; DAY ( TODAY () - 1 ) )
    )
RETURN
    CALCULATE (
        SUM ( Table1[sales] );
        FILTER (
            'Date';
            'Date'[Date]
                = DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ) - 1; DAY ( TODAY () - 1 ) )
        )
    )

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

Hi @Anonymous,

 

The DATEADD function only accepts one column as parameter try the following measure I have already made it to work with change of year.

 

PMOM =
VAR Date_select =
    IF (
        MONTH ( TODAY () ) = 1;
        DATE ( YEAR ( TODAY () ) - 1; 12; DAY ( TODAY () - 1 ) );
        DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ) - 1; DAY ( TODAY () - 1 ) )
    )
RETURN
    CALCULATE (
        SUM ( Table1[sales] );
        FILTER (
            'Date';
            'Date'[Date]
                = DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ) - 1; DAY ( TODAY () - 1 ) )
        )
    )

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



tjlee
Frequent Visitor

Try this formula

EOMONTH(TODAY(),-1)

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.