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
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?
Solved! Go to Solution.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsTry this formula
EOMONTH(TODAY(),-1)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
106 | |
91 | |
67 |
User | Count |
---|---|
162 | |
133 | |
133 | |
93 | |
90 |