Sum previous Month and current year return wrong numbers

[Anonymous]

Hi All,

I have two tables (UserActions & Date). Auto date/time is disabled.

I  want to create a simple measures but with no success.

Calculate YTD (Current Year) sum of t_UserActions (disregard all date filter)

calculate previous month of t_UserActions (disregard all date filter)

Should be able to filter both measures per Partner_ID.

Will greatly appreciate your help,

Thx,

Guy

[amitchandak]

Time intellignce and dates* and total* function should help

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))


last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))


MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous]

Thanks for your prompt reply.

The issue I still have is the total sum changed when filter on the page is selected. I want two cards in the page last month transactions (t_UserActions) and total transactions since begining of the year --> and these cards should not change when selecting a filter.

Many thx,

Guy

[amitchandak · Accepted solution]

@Anonymous

Try like

Measure pervious month =
var _max = eomonth(date(year(today()),month(today())-1,year(today())),0)
var _min = date(year(today()),month(today())-1,1)
Return 
calculate(countrows(t_UserActions),all(date),date[Date]<=_max && date[date]>= _min,all(t_UserActions))

Measure since  Start of year =
var _min=date(year(today()),1,1)
var _max =today()
Return 
calculate(countrows(t_UserActions),all(date),date[Date]<=_max && date[date]>= _min,all(t_UserActions))

all(date) will remove date filter. all(t_UserActions) will all filter on this table

So use all(t_UserActions), only as per need

[Anonymous]

@amitchandak  Thousand thanks!

[TomMartens]

Hey @Anonymous ,

here is a DAX statement to calculate a measure without using time intelligence functions:

[SalesYTD] := 
CALCULATE (
    [Sales], 
    FILTER (
        ALL ( 'Date' ), 
        'Date'[Year] = MAX ( 'Date'[Year] )
            && 'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

The DAX statement was taken from here: https://www.daxpatterns.com/time-patterns/

The article covers almost anything about date related calculations.

Hopefully, this provides some new ideas.

Regards,

Tom