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.

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!!!!!