YTD Context in Matrix

[asebes]

Seems like this should be easy but can't quite get my DAX Measure to work.  All I need is a YTD row in my matrix based on the applicable month.

So my matrix shows the previous 6 months and need a YTD number based on column header.  For example:

YTD Oct-20 would by Jan-20 through Oct-20

YTD Dec-20 would be Jan-20 through Dec-20

YTD Jan-21 would be Jan-21 through Jan-21

YTD Mar-21 would be Jan-21 through Mar-20

data		Desired Output
date	data		Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21
Jan-20	5		290	341	396	59	122	189
Jan-20	6
Feb-20	7
Feb-20	8
Mar-20	9
Mar-20	10
Apr-20	11
Apr-20	12
May-20	13
May-20	14
Jun-20	15
Jun-20	16
Jul-20	17
Jul-20	18
Aug-20	19
Aug-20	20
Sep-20	21
Sep-20	22
Oct-20	23
Oct-20	24
Nov-20	25
Nov-20	26
Dec-20	27
Dec-20	28
Jan-21	29
Jan-21	30
Feb-21	31
Feb-21	32
Mar-21	33
Mar-21	34
Apr-21	35
Apr-21	36
May-21	37
May-21	38

[v-yiruan-msft · Accepted solution]

Hi @asebes ,

You can create a measure as below and apply a relative date slicer, please find the details in the attachment.

Measure = 
VAR _curdate =
    SELECTEDVALUE ( 'Table'[date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[data] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[date] >= DATE ( YEAR ( _curdate ), 1, 1 )
                && 'Table'[date] <= EOMONTH ( _curdate, 0 )
        )
    )

Best Regards

[asebes]

works great! thanks for the expertise!

[amitchandak]

@asebes , For YTD you can use measure like this with date table

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

Now for 6 months the best that you use a relative date filter. https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

Or use an independent date table to select date range : https://www.youtube.com/watch?v=duMSovyosXE

[asebes]

But won't that measure be filtered on the month's in the column header and only give me that month's respective data?

What I need, for example, if the header month is Dec-20 then I need a sum of the transactions from 1/1/20 through 12/31/20.  If the header is Feb-21, then would need a sum of the transactions from 1/1/21 through 2/28/21.

Hope that makes sense, thanks!