Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Help Needed with DAX Formula for Custom Calendar Month and Previous Month Data

Hi Community,

 

I hope you’re doing well.

 

I’m working with a custom calendar in Power BI and need assistance displaying data for the current month and the previous month in a dashboard. Below is an example of my custom calendar setup:

 

  • January: 4 weeks (Start Date: 30th Dec 2024, End Date: 26th Jan 2025)
  • February: 4 weeks (Start Date: 27th Jan 2025, End Date: 23rd Feb 2025)
  • March: 5 weeks (Start Date: 24th Feb 2025, End Date: 30th Mar 2025)

This pattern continues for subsequent quarters.

My Goal

I want to display the current month's order data alongside the previous month's order data in a Power BI dashboard.

Issue Faced

I tried using the following DAX formula:
CALCULATE(COUNT(Sales[Date]), PREVIOUSMONTH(Calendar[Date]))

However, the result is returning as blank.

Request for Help

Could you please guide me on how to write the correct DAX formula to handle this scenario with a custom calendar?

Thank you in advance for your support!

 

Best regards,

VK



Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    Since you've set up the custom calendar in your report, you can't use PREVIOUSMONTH() beacause it filters the natural calendar instead of the custom calendar.

    As a workaround, you can add a custom month column and custom year column to filter the previous month's data.

    Here's an example,

    Create a calculated column to return the custom month.

    CustomMonth = SWITCH(TRUE(),
[Date]>=DATE(2024,12,30)&&[Date]<=DATE(2025,1,26),1,
[Date]>=DATE(2024,1,27)&&[Date]<=DATE(2025,2,23),2,
[Date]>=DATE(2024,2,24)&&[Date]<=DATE(2025,3,30),3)

    PreviousMonthSale = CALCULATE(SUM('Table'[Sale]),FILTER(ALLSELECTED('Table'),[CustomMonth]=MAX('Table'[CustomMonth])-1))

    Best Regards,
    Stephen Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    1 year ago

    Hello Anonymous,

     

    Can you please try this approach:

    CurrentMonthOrders =
CALCULATE(
    SUM(Sales[Order Amount]),
    FILTER(
        ALL(Calendar),
        Calendar[Month Start Date] <= MAX(Calendar[Date]) &&
        Calendar[Month End Date] >= MAX(Calendar[Date])
    )
)

    PreviousMonthOrders =
CALCULATE(
    SUM(Sales[Order Amount]),
    FILTER(
        ALL(Calendar),
        Calendar[MonthIndex] = MAX(Calendar[MonthIndex]) - 1
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

     

    Since you've set up the custom calendar in your report, you can't use PREVIOUSMONTH() beacause it filters the natural calendar instead of the custom calendar.

    As a workaround, you can add a custom month column and custom year column to filter the previous month's data.

    Here's an example,

    Create a calculated column to return the custom month.

    CustomMonth = SWITCH(TRUE(),
[Date]>=DATE(2024,12,30)&&[Date]<=DATE(2025,1,26),1,
[Date]>=DATE(2024,1,27)&&[Date]<=DATE(2025,2,23),2,
[Date]>=DATE(2024,2,24)&&[Date]<=DATE(2025,3,30),3)

    PreviousMonthSale = CALCULATE(SUM('Table'[Sale]),FILTER(ALLSELECTED('Table'),[CustomMonth]=MAX('Table'[CustomMonth])-1))

    Best Regards,
    Stephen Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly