Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
stuck in this solution.
I have simple sales table,
in dashboard I have these visuals
I want to have values by last date (march 2023) when nothing is selected , but YTD PY is showing the sum of whole previous year 12 and it should be 3. Also I want to filter right visual with column chart (if I click on february second visual should show YTD 2 and YTD PY also 2. and so on) hope you understand the task.
here's PBIX file https://1drv.ms/u/s!Aucu3BmZNQw-qZV1Vuayls4KBsNEzw?e=5Iaekq
Solved! Go to Solution.
@GiorgiPBI , Try if this formula can help, you can use the max date from table/fact in place of today
YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
LYTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
@GiorgiPBI , Try if this formula can help, you can use the max date from table/fact in place of today
YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
LYTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |