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-20YTD Dec-20 would be Jan-20 through Dec-20YTD Jan-21 would be Jan-21 through Jan-21YTD Mar-21 would be Jan-21 through Mar-20 data Desired Outputdatedata Oct-20Nov-20Dec-20Jan-21Feb-21Mar-21Jan-205 29034139659122189Jan-206 Feb-207 Feb-208 Mar-209 Mar-2010 Apr-2011 Apr-2012 May-2013 May-2014 Jun-2015 Jun-2016 Jul-2017 Jul-2018 Aug-2019 Aug-2020 Sep-2021 Sep-2022 Oct-2023 Oct-2024 Nov-2025 Nov-2026 Dec-2027 Dec-2028 Jan-2129 Jan-2130 Feb-2131 Feb-2132 Mar-2133 Mar-2134 Apr-2135 Apr-2136 May-2137 May-2138

Forum Discussion

Frequent Visitor

5 years ago

Solved

YTD Context in Matrix

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

Need Help

Anonymous

5 years ago

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

YTD Context in Matrix.pbix50 KB

4 Replies

amitchandak
Super User
5 years ago
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
  Frequent Visitor
  5 years ago
  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!
Anonymous
Not applicable
5 years ago
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
YTD Context in Matrix.pbix50 KB
- asebes
  Frequent Visitor
  5 years ago
  works great! thanks for the expertise!

Forum Discussion

YTD Context in Matrix

4 Replies

Related content

Matrix displaying quarterly, YTD, YTD PY, and YOY Var

DAX Calculate context - creating own YTD

Context Transition in DAX

Not passing context to Tooltip page

Matrix table