Need help with the DAX

[Avivek]

I have a projection measure, previously it used to calculate the actual proj month usage + last 2 month usage, i want to make few changes to it. Instead of the last 2 month i want to use last n month usage. The last n month usage  should use the following logic-

1. When Oct is selected, show only Oct projection
2. Sep and Oct, show only Oct projection as Tot.Proj
3. Aug, Sep and Oct, show Sep+Oct as tot proj
4. July, Aug, Sep and Oct, show July+Sep+Oct as tot proj  (current prod)

Can anyone help me with this dax please, i aa not sure how to start it at the 1st place.

[PaulDBrown]

@Avivek 

Can you please post the measure you are using currently?

[Avivek]

The current measure I use is -

Tot. Act. proj. Mon. Usage = [Act. proj. Mon. Usage]+[Act. Last2 Mon Comp.Usage]

Act. Last2 Mon Comp.Usage = CALCULATE(count('DO Vs Tag'[Procedure Credit Owner]), FILTER('DO Vs Tag','DO Vs Tag'[Last 2 Mon Procedure]=1))

The measure I need to modify is Tot. Act. proj. Mon. Usage.

[v-jingzhang]

Hi @Avivek 

You could add a month number column in your table and try a measure like below:

Last N Total = 
VAR currentMonth = MAX('Table (2)'[Month Number])
VAR minSelectedMonth = MIN('Table (2)'[Month Number])
VAR n = currentMonth - minSelectedMonth
RETURN
IF(n=0,
CALCULATE([Total],FILTER(ALL('Table (2)'),'Table (2)'[Month Number] = currentMonth)),
CALCULATE([Total],FILTER(ALL('Table (2)'),'Table (2)'[Month Number] > minSelectedMonth && 'Table (2)'[Month Number] <= currentMonth))
)

 My [Total] measure in this example is a simple measure Total = SUM('Table (2)'[Value]) where you can change to yours. 

Hope this post helps.

Best regards,

Jing

[Avivek]

@v-jingzhang , thankyou for the response. But when you say Value which value are you referring to. If you refer Act. proj. Mon. Usage as the table the thing is it is a measure. So in that case I think i may not not be able to use Filter All option.

Please let me know if it may work or I may need a new measure all together.

[v-jingzhang]

@Avivek, I'm sorry I didn't make it clear to you. I didn't know what your data table is like so I created some sample data to give this example measure. My sample data is like below, so the Value is simply the Value column in this table. You will need to modify the measure according to your data table.

Could you please share some sample data and what you expect it to display in the report? How will you select the months? (By slicer, filter pane or something else?) Thus I will probably be able to help modify the measures accordingly.

Regards,

Jing