Date expression current month, prior month, prior quarter

[ugurarslan]

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

[MK_QSL]

What is the logic behind these dates?

[ugurarslan]

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

[MK_QSL]

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..

[OmarBenSalem]

Imagine you have this measure:

sum(Measure)

1) Current Month:

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

2) previous Month :

sum({<Date={"$(=Addmonths(max(Date),-1))"}>}Measure)

3)Previous Year:

sum({<Date={"$(=AddYears(max(Date),-1))"}>}Measure)