4 Replies Latest reply: Mar 14, 2018 3:41 PM by omar bensalem

# Date expression current month, prior month, prior quarter

Dear,

I have date dimension which is as following:

ProdDate         ProdTotal

12/1/2017        4434

1/1/2018          3234

2/1/2018          4352

3/1/2018          5433

4/1/2018          4456

I have 4 KPI Chart which need to update automatically with the new number, at this moment I use the following expressions which I need to update manually every month.

How can I automate this that it updates every month with the new data? (add months)

Current Month ProdTotal    Sum({<[ProdDate]={'3/1/2018'}>}[ProdTotal])

Prior Month ProdTotal        Sum({<[ProdDate]={'2/1/2018'}>}[ProdTotal])

Prior Quarter ProdTotal      Sum({<[ProdDate]={'11/1/2018'}>}[ProdTotal])

Prior Year ProdTotal           Sum({<[ProdDate]={'3/1/2017'}>}[ProdTotal])

Thanks

Ugur

• ###### Re: Date expression current month, prior month, prior quarter

What is the logic behind these dates?

• ###### Re: Date expression current month, prior month, prior quarter

Hi Manish, it is an excel file which a date field and a measurement, very simple.

• ###### Re: Date expression current month, prior month, prior quarter

It's not simple...!!

1) You haven't mentioned how you are getting dates?

2) to update automatically with the new number? Which new numbers?

Current Month ProdTotal    Sum({<[ProdDate]={'3/1/2018'}>}[ProdTotal])

Prior Month ProdTotal        Sum({<[ProdDate]={'2/1/2018'}>}[ProdTotal])

Prior Quarter ProdTotal      Sum({<[ProdDate]={'11/1/2018'}>}[ProdTotal])

Prior Year ProdTotal           Sum({<[ProdDate]={'3/1/2017'}>}[ProdTotal])

Also, not understood, why you have used those dates here? For 3rd expression, you have 11/1/2018 which is not even in sample data..

• ###### Re: Date expression current month, prior month, prior quarter

Imagine you have this measure:

sum(Measure)

1) Current Month:

sum({<Date={"\$(=max(Date))"}>}Measure)

2) previous Month :

3)Previous Year: