Forum Discussion
DAX calculation based on time period selection
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）
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
3 Replies
- amitchandak
Super User
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])))
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
