DAX calculation based on time period selection

[Anonymous]

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）

[v-yangliu-msft · Accepted 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

[amitchandak]

@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])))

[Anonymous]

Thanks, it seems easy to get the accumulative value, Is it possible to get calculation 2? Thanks!