Need help on the calculation

[Anonymous]

Hi Experts,

I needa help on the calculation.

Here is the example. I want o created a Column and display the latest data in the month.

Here, I want to created a Column as "YTD Act"  and display the latest values, i.e - in the sample data have been updated till Aug month column, so in the YTD Act column the values should be 

0

57

0

0

57

5

0

Please help me to acheive this.

Regards,

Swamy

[v-lili6-msft]

hi, @Anonymous 

First, you must know that calculated column and calculate table can't be affected by any slicer. 
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer, It only be affected when refresh dataset.
2. measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, What is your data structure in basic table, If Month columns are in one field and your screenshot is based on a matrix visual, Then it could be achieved like below:

https://community.powerbi.com/t5/Desktop/Add-column-to-matrix-visual/td-p/600063

If Month columns are different fields, your screenshot is just the data structure, your requirement couldn't be achieved directly.

Since the "Month" column are just column name, It can't tell which column is the last month.

So you may try to unpivot the Month columns data, then get it in matrix visual by the above solution.

Regards,

Lin

[JirkaZ]

@Anonymous Isn't that what exactly TOTALYTD DAX function does? 
Please check the docummentation: https://docs.microsoft.com/en-us/dax/totalytd-function-dax

[Anonymous]

Hi,

No, I have created month column as calculated and i want to get the latest month's value.

Regards

swamy

[JirkaZ]

@Anonymous Why do you have it as a calculated column? Don't you have a date table in your model that you could use for that? Then the TOTALYTD would work.

[Anonymous]

Hi,

Yes I  have, but the problem is there a different logic for each country in the report, so i have to arrive the expected the results based on the logic for that i have to create the calculated column. No other way.

[JirkaZ]

@Anonymous Could you please clarify the various rules for certain countries? There might be a way around that.