Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Dynamic calculation based on Date selection

Hello Team,

 

I have a separate date table which has a column like distinct(datasource[CutoffDate]). then I use this column to get calculation based on the month as shown below. **( cut off date coming from my data source)

 

ZF_PM-2 = VAR CurrentMonth = MAX(Datetable2[MyDate])
VAR PreviousDate = FORMAT(DATE(YEAR(CurrentMonth), MONTH(CurrentMonth) - 2, 1),"yyyy-MM")
VAr Result =
CALCULATE(
SUM(FACT_IA_IIIO_XQ227[ProvAmt]),
FILTER(FACT_IA_IIIO_XQ227,
 
FORMAT(FACT_IA_IIIO_XQ227[Audit End(CutOffDate)],"yyyy-MM") = PreviousDate
)
)
Return
Result
 
I have this calculation from Current month till previous month -10.  right now my current month is July. I then use my date column as a slicer to filter my desired date range.
 
So if we use the slicer to take feb'19, my Current month should change to feb'19  and respectively other months. but it does not happen, the current month stays July no matter what is the selection on the slicer.
Need Help
Show and Tell
Tips and Tricks

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      yes, the slicer is based on the myDate  table.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        HI Anonymous ,

        Current power bi not support to create dynamic column/table based on slicer/filter. Please modify your formula to write a measure to achieve your requirement.

        Regards,

        Xiaoxin Sheng