Need urgent help please

[Swamy3105]

Hi Experts,

I need realy urgent help.

I have the data with Actual and Plan with 2018 Whole year data, and 2019 with the current month (March) data.

I just calculated the max month, but it's not working. because when i select the 2019 in the slicer it's working fine, because i have the data till march, but when i select the 2018, it's showing the Decemeber month's data, because in 2018 the max month is December. even if I select the 2018 data it should show the March month data only.

How to do that, please help, I have a POC with my stakeholder tomorrow. I do not want to add the month slicer.

Please help......

[PattemManohar]

@Swamy3105  If I've understood your problem statement correctly, you always want to show the current month data for previous years as well (even if the data is available for whole year). It will be really helpful and quicker if you can share the sample test data and expected output.

[Swamy3105]

Hi,

Yes you are correct.

Year           Period           Amount

2018             1                     100

2018             2                     200

2018             3                     300  

2018             4                     400

2018             5                     500

2018             6                     600

2018             7                     700

2018             8                     800

2018             9                     900

2018            10                    1000

2018            11                     500

2018            12                     750

2019             1                      540 

2019             2                      610 

If select the year in the slicer as 2019 the max month is for 2019 = 2 and the value should be 610

If select the year in the slicer as 2018 the value should be 200 which is Feb month value

[PattemManohar · Accepted solution]

@Swamy3105  Thanks for the sample data.  Please add a New Column to the table as below

MaxFlag = 
VAR _MaxYear = MAX(Test299Slicer[Year])
VAR _MaxPeriod = MAXX(FILTER(Test299Slicer,Test299Slicer[Year]=_MaxYear),Test299Slicer[Period])
RETURN IF(Test299Slicer[Period]=_MaxPeriod,"Y","N")

Then add a New Measure as below

Test299 = CALCULATE(SUM(Test299Slicer[Amount]),Test299Slicer[MaxFlag]="Y")

[Swamy3105]

one more help.

how to check the min period using the same logic.

[PattemManohar]

@Swamy3105  If you want to have Min period then it will be same logic with slight modifications.... Add new column as below

MinFlag = 
VAR _MaxYear = MAX(Test299Slicer[Year])
VAR _MinPeriod = MINX(FILTER(Test299Slicer,Test299Slicer[Year]=_MaxYear),Test299Slicer[Period])
RETURN IF(Test299Slicer[Period]=_MinPeriod,"Y","N")

Then the New Measure will be

Test299Min = CALCULATE(SUM(Test299Slicer[Amount]),Test299Slicer[MinFlag]="Y")

[Swamy3105]

Excellent Man!!!!!

This is what i expected, really thank you so much !

[Johannesvd]

Hi,

If you use a date table add a column relative nr of months example:
https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/ 

this way you can create a dynamic measure and always display the current month (Example relative month = 0)

this will make your life a lot easier (also you can compare months a lot easier 0 and 12 are the same months in different years).