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
WaelTalaat79
Helper I
Helper I

Hi dear

I am trying to calculate a if condition for previousmonth and I got this message 

MdxScript(Model) (13, 76) Calculation error as attached photo 

11B6C880-2AFA-49BC-97F1-C431E8BA6A76.jpeg

6 REPLIES 6
v-luwang-msft
Community Support
Community Support

Hi @WaelTalaat79 ,

You have to read the fine print here to understand how PREVIOUSMONTH (and similar functions) work:

https://support.office.com/en-US/article/PREVIOUSMONTH-Function-DAX-72fb8dda-6cd6-49e6-b9b4-8a5f6324...

 

"This function returns all dates from the previous month, using the first date in the column used as input. For example, if the first date in the dates argument refers to June 10, 2009, this function returns all dates for the month of May, 2009."

 

So, mainly you have to use this in a "context aware way". Here is the example from the page:

 

Example

The following sample formula creates a calculated field that calculates the 'previous month sales' for the Internet sales.

To see how this works, create a PivotTable and add the fields, CalendarYear and MonthNumberOfYear, to the Row Labels area of the PivotTable. Then add a calculated field, named Previous Month Sales, using the formula defined in the code section, to the Values area of the PivotTable.

=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSMONTH('DateTime'[DateKey]))

 

And you could use the measure to get last month data:

 

lastmonth =
IF (
    MONTH ( MAX ( 'Date'[Date] ) ) = 1,
    DATE ( YEAR ( MAX ( 'Date'[Date] ) ) - 1, 12, DAY ( MAX ( 'Date'[Date] ) ) ),
    DATE ( YEAR ( MAX ( 'Date'[Date] ) ), MONTH ( MAX ( 'Date'[Date] ) ), DAY ( MAX ( 'Date'[Date] ) ) )
)

 

 

 

 

lastmon2 = IF (
    MONTH ( MAX ( 'Date'[Date] ) ) = 1,12,0
    
   
)

 

vluwangmsft_0-1654571244679.png

 

And you could refer:

 
 

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


Best Regards

Lucien

Fowmy
Super User
Super User

@WaelTalaat79 

Modify your meusre as follows: 

= IF( MONTH( PREVIOUSMONTH( 'Dates'[Date].[Date] ) ) = 12 , 0 , 1 )



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

error message MdxScript(Model) (13, 72)

 

= 12 mean December so should I select MonthNo instead of date

@WaelTalaat79 

Where/what are you using this measure? Please share screenshot of the calcualtion 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

2BB38783-0AB7-495D-9184-62C869E151C4.jpeg

  •  this the calculated formula 
  • so I need the manthly impact by subtracting  the calculated month - previous month
  • but the problem in month of Jan. that considering the previous month as December that should be always Zero and the previous month fromula bring December value as normal fromula
  • so I need that if the previous month is December put zero if not apply the fromulaB58D901C-F5E0-49A1-A012-FEE91B8AC0A5.jpeg

    And this is the previous month fromula

    F2D341F9-A890-4DD6-87B3-6F6B7F9094FB.jpeg

Hi,

Please describe the question and show the expected result.  Share the download link of your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.