Forum Discussion

IAA's avatar
IAA
Helper I
6 years ago
Solved

Dateadd with filter/if statement?

Dear community,

I have a problem with the following: 

I am creating a cashflow forecast, which is shown in the picture below. The idea is that when the parameter is 2, the date of paying/receving money is shifted two days further in the future.

 

I accomplished this with the following formula:

 

 

 

 

CALCULATE ( AVERAGE( 'Value by date 2'[Value] ),
    DATEADD( DimDate[Date], -[Value Parameter], DAY))

 

 

 

 

 

But now I want to combine this with the slicertable, so that when I select "personeelskosten", that only that specific bar shifts two days to the left, while the others stay at the same day (important)

Does anyone have advice how to adjust my formula?

The file can be downloaded here:
https://www.dropbox.com/s/3xs6wyr6tij9vnp/Cashflow%20Forecast.pbix?dl=0

Thank you very much in advance

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi IAA ，

     

    Create a slicer table as below:

    Slicer Table = DISTINCT('Value by date 2'[Category])

    Then create a measure as below:

    Measure = 
 IF(MAX('Value by date 2'[Category]) in FILTERS('Slicer Table'[Category]),CALCULATE ( AVERAGEX(FILTER(ALL('Value by date 2'),'Value by date 2'[Category]=SELECTEDVALUE('Slicer Table'[Category])), 'Value by date 2'[Value]),DATEADD( DimDate[Date], [Value Parameter], DAY)),CALCULATE(AVERAGE('Value by date 2'[Value]),
    DATEADD( DimDate[Date], [Value Parameter], DAY)))

    Finally you will see:

    For details,pls see attached.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

1 Reply

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi IAA ，

     

    Create a slicer table as below:

    Slicer Table = DISTINCT('Value by date 2'[Category])

    Then create a measure as below:

    Measure = 
 IF(MAX('Value by date 2'[Category]) in FILTERS('Slicer Table'[Category]),CALCULATE ( AVERAGEX(FILTER(ALL('Value by date 2'),'Value by date 2'[Category]=SELECTEDVALUE('Slicer Table'[Category])), 'Value by date 2'[Value]),DATEADD( DimDate[Date], [Value Parameter], DAY)),CALCULATE(AVERAGE('Value by date 2'[Value]),
    DATEADD( DimDate[Date], [Value Parameter], DAY)))

    Finally you will see:

    For details,pls see attached.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!