Forum Discussion

asebes's avatar
asebes
Frequent Visitor
5 years ago
Solved

Return Dynamic YTD Value Based on Slicer

Seems like this should be easy but can't quite get it.  Want to get a dynamic MTD/YTD amount based on the slicer value I choose. For example, using the data below, and the month Feb-20 is chosen, wa...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi asebes ,

    As far as possible, create a relationship between the data table applied the slicer and the DATA table. If it is not possible, you can try the following formula to create measures.

    MTD = 
var selectvalue = SELECTEDVALUE('Calendar'[Date].[MonthNo])
return 
  CALCULATE(
    SUM(DATA[Amount]),
    FILTER(
      DATA,
      DATA[Period].[MonthNo] = selectvalue
    )
  )
    YTD = 
var selectvalue = SELECTEDVALUE('Calendar'[Date].[MonthNo])
var t = 
  CALCULATE(
    SUM(DATA[Amount]),
    FILTER(
      ALL(DATA),
      DATA[Period].[MonthNo] <= selectvalue
    )
  )
return 
  IF(
    MINX(DATA,DATA[Period].[MonthNo]) = selectvalue,
    t,
    BLANK()
  )

     

    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
Icon for Super User rankSuper User
5 years ago

asebes , You have default year for this case and use time intelligence with date table

 

Create a column like this in your date table and filter on this https://www.youtube.com/watch?v=hfn05preQYA

 

Year Type = Switch( True(),
year([Date])= year(Today()),"This Year" ,
Format([Date],"YYYY")
)

 

 

TI Measures

 

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

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

 

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
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

 

 

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 :radacad sqlbi My Video Series Appreciate your Kudos.

asebes's avatar
asebes
Frequent Visitor
5 years ago

Thank you for input!  I'm still missing something though.  So if i have a slicer that details all the months of the year, but then want the YTD formula to be based on the slicer month i choose.  So if i chose March, the MTD column would be be March info, but the YTD column would give the sum of Jan, Feb & Mar.

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi asebes ,

    As far as possible, create a relationship between the data table applied the slicer and the DATA table. If it is not possible, you can try the following formula to create measures.

    MTD = 
var selectvalue = SELECTEDVALUE('Calendar'[Date].[MonthNo])
return 
  CALCULATE(
    SUM(DATA[Amount]),
    FILTER(
      DATA,
      DATA[Period].[MonthNo] = selectvalue
    )
  )
    YTD = 
var selectvalue = SELECTEDVALUE('Calendar'[Date].[MonthNo])
var t = 
  CALCULATE(
    SUM(DATA[Amount]),
    FILTER(
      ALL(DATA),
      DATA[Period].[MonthNo] <= selectvalue
    )
  )
return 
  IF(
    MINX(DATA,DATA[Period].[MonthNo]) = selectvalue,
    t,
    BLANK()
  )

     

    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.

    • asebes's avatar
      asebes
      Frequent Visitor
      5 years ago

      i have no idea how that formula works, but it does!  thank you!!!!!