Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculation with Slicer

Hello, 

 

I have the below date table (called Value Date) which is linked to a slicer on the right. 

 

 

 

I would like to calculate the difference between a chosen slicer date's total and the total on the last day of the recent end of the month. 

 

I have already created measures for them (below) but for some reason finding it hard to create a measure linking the first measure to the slicer. 

 

Total Portfolio Balance in GBP =
SUMX('CDP_DX V1_MonthlyTotals',
    'CDP_DX V1_MonthlyTotals'[PortfolioBalance] /
         LOOKUPVALUE('Daily FX Rate'[Value],
         'Daily FX Rate'[Date], 'CDP_DX V1_MonthlyTotals'[ReportingDate],
         'Daily FX Rate'[Currency], 'CDP_DX V1_MonthlyTotals'[CurrencyCode] ))

 

Total on last day of most recent EOM:

Value in GBP on last day of Month (Last Month) V1 =

  VAR __LastMonthDate = EOMONTH(TODAY(),-1)

  VAR __LastMonth = MAXX(FILTER('Value Date',[Date]=__LastMonthDate),[Total Portfolio Balance in GBP])

 

RETURN

__LastMonth

 

Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    Anonymous , for last day of recent month when slicer have some other value selected

     

    calculate([Total Portfolio Balance in GBP ], filter(all('Value Date'), 'Value Date'[Date] = eomonth(today(),-1) ) )

     

    assuming Value Date is a date table 

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , for last day of recent month when slicer have some other value selected

     

    calculate([Total Portfolio Balance in GBP ], filter(all('Value Date'), 'Value Date'[Date] = eomonth(today(),-1) ) )

     

    assuming Value Date is a date table