Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to write a dax YTD.

hi,

please help on this issues.

 

How to write a dax YTD.

 

Based in the user selection.

Eample user select 2018 that time 2018 ytd.

Eample user select 2017 that time 2018 ytd.

 

Datesales
11/8/201710
6/12/201720
10/11/201630
10/11/201640
6/9/201550
6/9/201560
6/9/201570
6/9/201580
6/9/201590
6/9/2015100
6/9/2015110
4/15/2018120

 

Interesting Links
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    6 years ago

    In case you have the continuous date and have Date dimension marked Date in Model. This will change based on the date on date slicer from date table

     

    Year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date Filer]))
Last Year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date Filer],-12,MONTH)))

    in case dates are not continuous

     

     

    On the Fly Change % = 
Var _last_year= (max('Date'[Date Filer]))-365
Var   _This_year=year(max('Date'[Date Filer]))
Var _min_last_year= (maxx('Date',STARTOFYEAR(DATEADD('Date'[Date Filer],-12,MONTH))))
Var   _min_This_year=year(max(STARTOFYEAR('Date'[Date Filer])))

Var _last_year_val= CALCULATE(sum(Sales[Sales Amount]),(Sales[Sales Date])<=_last_year && (Sales[Sales Date]) >=_min_last_year)
Var   _This_year_val =CALCULATE(sum(Sales[Sales Amount]),(Sales[Sales Date])<=_This_year && (Sales[Sales Date]) >=_min_This_year)
return
(_This_year_val-_last_year_val)/_last_year_val*100

     

     

     

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    In case you have the continuous date and have Date dimension marked Date in Model. This will change based on the date on date slicer from date table

     

    Year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date Filer]))
Last Year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date Filer],-12,MONTH)))

    in case dates are not continuous

     

     

    On the Fly Change % = 
Var _last_year= (max('Date'[Date Filer]))-365
Var   _This_year=year(max('Date'[Date Filer]))
Var _min_last_year= (maxx('Date',STARTOFYEAR(DATEADD('Date'[Date Filer],-12,MONTH))))
Var   _min_This_year=year(max(STARTOFYEAR('Date'[Date Filer])))

Var _last_year_val= CALCULATE(sum(Sales[Sales Amount]),(Sales[Sales Date])<=_last_year && (Sales[Sales Date]) >=_min_last_year)
Var   _This_year_val =CALCULATE(sum(Sales[Sales Amount]),(Sales[Sales Date])<=_This_year && (Sales[Sales Date]) >=_min_This_year)
return
(_This_year_val-_last_year_val)/_last_year_val*100