Forum Discussion

Uzi2019's avatar
Uzi2019
Community Champion
5 years ago
Solved

Incorrect Previous month value calculation

Hi Community,   I am trying to create previous month values. Everything is working as expected only issue is last date in Database is 5th April 2021 but when I select Date range slicer from 1st to ...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi Syndicate_Admin ,

    I create the following sample data, and then create a measure.

     

    Ventas anteriores = 
var MonthEnd = 
    CALCULATE(
        SUM('DataTable'[Sales]),
        DATEADD( 'Calendar'[Date], -1, MONTH )
    )
var Month = 
    CALCULATE(
        SUM('DataTable'[Sales]),
        FILTER(
            ALL('DataTable'),
            'DataTable'[Date] <= EDATE( MAX('Calendar'[Date]), -1 )
            &&'DataTable'[Date] >= EDATE( MIN('Calendar'[Date]), -1 )
        )
    )
var Result = 
    IF(
        MAX('Calendar'[Date]) = EOMONTH( MAX('Calendar'[Date]), 0 ), 
        MonthEnd, 
        Month
    )
return Result

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

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

amitchandak's avatar
amitchandak
Super User
5 years ago

Syndicate_Admin , You can try like example

 

MTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESMTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESMTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALMTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))


LMTD QTY forced=
var _max = date(year(today()),month(today())-1,day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALMTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

 

LYMTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALMTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

 

Refer

 

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA