Showing All Sales from a Selected Date in a Stacked Column Chart

[H_insight]

Hi All,

Attached is a sample file that contains Sales, product and dates tables. I have a stacked column chart which is showing total sales by-products for each month (See below)

I am trying to write a measure that will show all sales from the selected date (slicer). So when I choose Jun 20, the stacked chart will show data from Jan to Jun 20 only.

Any hints or help is much appreciated. 

Many thanks

Sample File Download 

[amitchandak]

@H_insight , In case you have date, Create YTD measure

example

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

use date table

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.

[H_insight]

Hi @amitchandak 

The above Dax will give me the YTD as a single column, but I want to show the monthly sales stacked in a column for each product. So when I select Jun 20, the stacked column will show sales form Jan-Jun 20.

[amitchandak · Accepted solution]

@H_insight , then you need an independent table.

With filter from that independent date table

YTD=
var _Max = maxx(allselected(Newdate), NewDate[Date])
var _min= datediff(year(_Max), 1,1)
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Day of Year] <= _max) )

refer for independent table

https://www.youtube.com/watch?v=duMSovyosXE

[H_insight]

Thanks for your help @amitchandak