Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sum previous Month and current year return wrong numbers

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

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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

     

5 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    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

     

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        amitchandak
        Super User
        6 years ago

        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