Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Need urgent help please

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...
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    Anonymous  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")

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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