Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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
works great! thanks for the expertise!
@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
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!