Calculating Current Month / Previous Month using CALCULATE / DATEADD Functions showing incorrectly

Hello

I have the following DAX to arrive at the current and prior month cost:

However, the total cost for the entire period, as opposed to the latest month (March 2024), in the data set is being totalled:

However, when I filter by Month (March 2024) I arrive at the correct figures:

Please help!

Many thanks!

If it helps, here's an example of a calender table I use quite frequently.

Calendar = 
VAR _today_date =        TODAY()  //'Properties'[Today Date]
VAR _fiscal_year =       YEAR(EDATE( _today_date, 6))
VAR _fiscal_year_start = DATE ( _fiscal_year - 1, 07, 01)
VAR _fiscal_year_end =   DATE ( _fiscal_year, 06, 30)

VAR _result = 
    ADDCOLUMNS
    (
        //CALENDARAUTO()   //range of dates is calculated automatically based on data in the model
        CALENDAR(_fiscal_year_start, _fiscal_year_end)
        , "Calendar Year Period End",   FORMAT([Date], "yyyy12")
        , "Calendar Year Period Start", FORMAT([Date], "yyyy01")
        , "Calendar Year Period",       FORMAT([Date], "yyyyMM")
        , "Calendar Year Quarter Nbr",  QUARTER([Date])
        , "Calendar Year Quarter",      FORMAT([Date], "\C\Yyyyy \Qq")
        , "Calendar Year Half",         FORMAT([Date], "\F\Yyyyy \H") & ROUNDUP(MONTH([Date]) / 6, 0)
        , "Calendar Year",              YEAR([Date])
        , "Day Name Short",             FORMAT([Date], "DDD")
        , "Day Name",                   FORMAT([Date], "DDDD")
        , "Day Of Week",                WEEKDAY([Date])  
        , "Day",                        DAY([Date])
        , "Fiscal Year Period End",     FORMAT(EDATE([Date], 6), "yyyy12")
        , "Fiscal Year Period Start",   FORMAT(EDATE([Date], 6), "yyyy01")
        , "Fiscal Year Period",         FORMAT(EDATE([Date], 6), "yyyyMM")
        , "Fiscal Year Quarter Nbr",    FORMAT(EDATE([Date], 6), "q")
        , "Fiscal Year Quarter",        FORMAT(EDATE([Date], 6), "\F\Yyyyy \Qq")
        , "Fiscal Year Half",           FORMAT(EDATE([Date], 6), "\F\Yyyyy \H") & ROUNDUP(MONTH(EDATE([Date], 6)) / 6, 0)
        , "Fiscal Year",                YEAR(EDATE([Date], 6))
        , "Is Current FY",              IF(YEAR(EDATE([Date], 6)) = _fiscal_year, 1, 0)
        , "Is Future",                  IF([Date] > _today_date, 1, 0)
        , "Month End",                  EOMONTH([Date], 0)
        , "Month Name Short",           FORMAT([Date], "MMM")
        , "Month Name",                 FORMAT([Date], "MMMM")
        , "Month Start",                DATE(YEAR([Date]), MONTH([Date]), 1)
        , "Month",                      MONTH([Date])
        , "Week Ending",                [Date] + 7 - WEEKDAY([Date], 1) // Saturday
        , "Week Starting",              [Date] - WEEKDAY([Date], 1) + 1 // Sunday
        , "Week of Month",              1 + WEEKNUM([Date]) - WEEKNUM( EOMONTH([Date], -1 ) + 1 )
        , "Week of Year",               WEEKNUM([Date])
        , "Week of Fiscal Year",        IF(MONTH([Date]) < 7
                                        , WEEKNUM([Date], 1) + (WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) - 1)
                                        , WEEKNUM([Date], 1) - WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) + 1)
    )

RETURN
_result

5 Replies

aduguid

Memorable Member

2 years ago

Current Month Cost = 
CALCULATE(
    [Total Cost], 
    FILTER(
        ALL('Date'),
        'Calendar'[Calendar Year] = YEAR(TODAY()) &&
        'Calendar'[Month] = MONTH(TODAY())
    )
)

Previous Month Cost = 
CALCULATE(
    [Total Cost],
    PREVIOUSMONTH('Calendar'[Date])
)

kc_

Helper I

2 years ago

Thanks for the prompt response, however, it has not resolved the issue.

The Previous month calculation is not returning any values and for the current month I am getting the following message:

I suspect that this is because I am using CALENDARA() and have then used the FORMAT to arrive at Year and Month.

I will see if I can get the files across (may take some time) ...the issue can be easily resolved by using the Filter pane and this will serve the purpose (at least for now).

aduguid

Memorable Member

2 years ago

If it helps, here's an example of a calender table I use quite frequently.

Calendar = 
VAR _today_date =        TODAY()  //'Properties'[Today Date]
VAR _fiscal_year =       YEAR(EDATE( _today_date, 6))
VAR _fiscal_year_start = DATE ( _fiscal_year - 1, 07, 01)
VAR _fiscal_year_end =   DATE ( _fiscal_year, 06, 30)

VAR _result = 
    ADDCOLUMNS
    (
        //CALENDARAUTO()   //range of dates is calculated automatically based on data in the model
        CALENDAR(_fiscal_year_start, _fiscal_year_end)
        , "Calendar Year Period End",   FORMAT([Date], "yyyy12")
        , "Calendar Year Period Start", FORMAT([Date], "yyyy01")
        , "Calendar Year Period",       FORMAT([Date], "yyyyMM")
        , "Calendar Year Quarter Nbr",  QUARTER([Date])
        , "Calendar Year Quarter",      FORMAT([Date], "\C\Yyyyy \Qq")
        , "Calendar Year Half",         FORMAT([Date], "\F\Yyyyy \H") & ROUNDUP(MONTH([Date]) / 6, 0)
        , "Calendar Year",              YEAR([Date])
        , "Day Name Short",             FORMAT([Date], "DDD")
        , "Day Name",                   FORMAT([Date], "DDDD")
        , "Day Of Week",                WEEKDAY([Date])  
        , "Day",                        DAY([Date])
        , "Fiscal Year Period End",     FORMAT(EDATE([Date], 6), "yyyy12")
        , "Fiscal Year Period Start",   FORMAT(EDATE([Date], 6), "yyyy01")
        , "Fiscal Year Period",         FORMAT(EDATE([Date], 6), "yyyyMM")
        , "Fiscal Year Quarter Nbr",    FORMAT(EDATE([Date], 6), "q")
        , "Fiscal Year Quarter",        FORMAT(EDATE([Date], 6), "\F\Yyyyy \Qq")
        , "Fiscal Year Half",           FORMAT(EDATE([Date], 6), "\F\Yyyyy \H") & ROUNDUP(MONTH(EDATE([Date], 6)) / 6, 0)
        , "Fiscal Year",                YEAR(EDATE([Date], 6))
        , "Is Current FY",              IF(YEAR(EDATE([Date], 6)) = _fiscal_year, 1, 0)
        , "Is Future",                  IF([Date] > _today_date, 1, 0)
        , "Month End",                  EOMONTH([Date], 0)
        , "Month Name Short",           FORMAT([Date], "MMM")
        , "Month Name",                 FORMAT([Date], "MMMM")
        , "Month Start",                DATE(YEAR([Date]), MONTH([Date]), 1)
        , "Month",                      MONTH([Date])
        , "Week Ending",                [Date] + 7 - WEEKDAY([Date], 1) // Saturday
        , "Week Starting",              [Date] - WEEKDAY([Date], 1) + 1 // Sunday
        , "Week of Month",              1 + WEEKNUM([Date]) - WEEKNUM( EOMONTH([Date], -1 ) + 1 )
        , "Week of Year",               WEEKNUM([Date])
        , "Week of Fiscal Year",        IF(MONTH([Date]) < 7
                                        , WEEKNUM([Date], 1) + (WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) - 1)
                                        , WEEKNUM([Date], 1) - WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) + 1)
    )

RETURN
_result

kc_
Helper I
2 years ago
Hello I'm still experiencing issues, please help!

I have the files (Excel + PBix) I can share if someone could have a look, alternatively, I will try and provide as much detail below....

My data is as follows:

Below is a table showing revenue by month:

In essence I want to show a card that shows the Current and Previous Month Revenue. Please note that the data only goes up to May 2024.

As there is an issue with using the current month DAX provided, I have opted instead to use the following DAX to calculate the current month:

Current MTD = TOTALMTD([Total Revenue],DATESMTD('Date'[Date]))

However, it returns the following:
If I delete the date relationship between the data set and the date table, the card will return the grand total of £86m (as above).

I am using the following DAX (as provided) to calculate the previous month:

Previous Month Cost =
CALCULATE(
[Total Revenue],
PREVIOUSMONTH('Date'[Date]
))

However, it returns the following:
- kc_
  Helper I
  2 years ago
  I seem to have had a breakthrough...
  
  I am using the following DAX:
  
  Current Month Revenue v3 =
  CALCULATE([Total Revenue],
  YEAR('Jan23 to Date'[Period]) = YEAR(MAX('Jan23 to Date'[Period])),
  MONTH('Jan23 to Date'[Period]) = MONTH(MAX('Jan23 to Date'[Period])))
  
  This returns what I am expecting:
  However, I am referencing the date to date column of the data set and NOT the Date Table which I have created in Power BI.
  
  When linking to the Date Table, wherein I have used CALENDARAUTO(), the dates run up to December 2024 and hence when using the previous DAX it was returning a Blank value.
  
  Unless I am missing something, I'd have thought that PowerBI would have recognised that the date in data set only goes up to May 2024...
  
  Kindly advise...

Forum Discussion

Calculating Current Month / Previous Month using CALCULATE / DATEADD Functions showing incorrectly

5 Replies

Related content

Commitment value calculating incorrectly - DAX Logic

If Statement Calculated Column Returning Incorrectly

MoM calculation

Calculation Help

Calculate Tooltips