Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I am new to use DAX, and I want to create two calculation to calculate metrics,
cal_1 is to calculation the acculative forecast value based start and end date selected,
cal_2 is when date is prior to present then select actual, if later than now then select forecast, and sum the values, and should also consider the date slicer
for example:
start_month_silcer=202206 end_month_silcer=2022010, and current date is 202208,
so the cal_1 (accumulative value of forecast) is 3, 10, 12, 17, 20, which is from 202206 to 202210
cal_2 value is (7+3+2 + 5+3)
Solved! Go to Solution.
Hi @Anonymous ,
Here are the steps you can follow:
1. Create calculated table.
Table 2 =
DISTINCT('Table'[date])
2. Create measure.
Flag =
var _min=MINX(ALLSELECTED('Table 2'),[date])
var _max=MAXX(ALLSELECTED('Table 2'),[date])
return
IF(
MAX('Table'[date])>=_min&&MAX('Table'[date])<=_max,1,0)
ca1 =
CALCULATE(
SUM('Table'[forcast]),
FILTER(ALLSELECTED('Table'),'Table'[date]<=MAX('Table'[date])))
ca2 =
var _today=TODAY()
return
SUMX(FILTER(ALLSELECTED('Table'),
'Table'[date]>=MINX(ALLSELECTED('Table'),'Table'[date])&&'Table'[date]<=_today),[actual])
+
SUMX(FILTER(ALLSELECTED('Table'),
'Table'[date]>_today&&'Table'[date]<=MaxX(ALLSELECTED('Table'),'Table'[date])),[forcast])
3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Anonymous ,
Here are the steps you can follow:
1. Create calculated table.
Table 2 =
DISTINCT('Table'[date])
2. Create measure.
Flag =
var _min=MINX(ALLSELECTED('Table 2'),[date])
var _max=MAXX(ALLSELECTED('Table 2'),[date])
return
IF(
MAX('Table'[date])>=_min&&MAX('Table'[date])<=_max,1,0)
ca1 =
CALCULATE(
SUM('Table'[forcast]),
FILTER(ALLSELECTED('Table'),'Table'[date]<=MAX('Table'[date])))
ca2 =
var _today=TODAY()
return
SUMX(FILTER(ALLSELECTED('Table'),
'Table'[date]>=MINX(ALLSELECTED('Table'),'Table'[date])&&'Table'[date]<=_today),[actual])
+
SUMX(FILTER(ALLSELECTED('Table'),
'Table'[date]>_today&&'Table'[date]<=MaxX(ALLSELECTED('Table'),'Table'[date])),[forcast])
3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@Anonymous , slicer date is coming from a common date table joined with forecast then
Cumm = CALCULATE(SUM(Table[forecast]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))
Thanks, it seems easy to get the accumulative value, Is it possible to get calculation 2? Thanks!
User | Count |
---|---|
98 | |
89 | |
82 | |
71 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |