Forum Discussion

nisuomi's avatar
nisuomi
Icon for Resolver I rankResolver I
7 years ago
Solved

Show only latest month data value

Hi,

 

There is a lot of asks for almost with the same header, but I could not find the solution to my problem.

So I have data and I want to only show the sum of things for the latest month only. So if I create a table, insert yearmonth value and then the measure, it should only show the measure value for the latest month that it can find from the data - nothing else.

 

So I have tried this:

Last Balance =
VAR endDate1 = MAXX(Table1;Table1[vuosikk])
return
CALCULATE(
SUM(Table1[peruslaskukaava]);
Table1[vuosikk] = endDate1
)

 

but only gets me to the way, where it shows the latest month value only for the year level per each..

And does not hide value for other months and show only for the latest.

 

This is what I have:

vuosi vuosikk Last Balance

2017 201701 380
2017 201711 -2000
2017 201712 400
2018 201801 380
2018 201802 -370
2018 201803 390

 

when it should be

vuosi vuosikk Last Balance

2017 201701 
2017 201711 
2017 201712 
2018 201801 
2018 201802 
2018 201803 390

 

 

Can't get my head around this, should not be so hard..

 

 

 

Cheers,

Niko

Need Help
  • nisuomi's avatar
    nisuomi
    7 years ago

    Hi PattemManohar,

     

    With this 

     

    Last Balance =
    VAR _MaxDate = MAXX(ALL(Table1[vuosikk]);Table1[vuosikk])
    VAR _Result = IF(FIRSTNONBLANK(Table1[vuosikk];1)=_MaxDate ; sum(Table1[peruslaskukaava]);BLANK())
    RETURN _Result

     

    I got it working, that it shows only for the latest yearmonth value. But in this option, I have to click from the table "rows" dimension "show items with no value" to show it the way I want it to be seen.

     

    vuosi vuosikk Last Balance
    2018 201803 390

     

    vuosi vuosikk Last Balance
    2017 201701 (Blank)
    2017 201711 (Blank)
    2017 201712 (Blank)

    2018 201801 (Blank)

    2018 201802 (Blank)

    2018 201803 390

     

     

    Thanks for your input tho'! It got my brains rolling and was helpful for me :) If you have any other solution, please, share!

     

     

     

    Cheers,
    Niko

4 Replies

  • PattemManohar's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    7 years ago

    nisuomi Please try this...

     

    Test39Out = 
VAR _MaxDate = MAX(Test39[YearMonth])
VAR _Result = SELECTCOLUMNS(Test39,"Year",Test39[Year],"YearMonth",[YearMonth],"LastBalance",IF(Test39[YearMonth]=_MaxDate,LOOKUPVALUE(Test39[Balance],Test39[YearMonth],_MaxDate),BLANK()))
RETURN _Result

    • nisuomi's avatar
      nisuomi
      Icon for Resolver I rankResolver I
      7 years ago

      Hello PattemManohar

       

      I guess I did not make clear enough start post, so my intention is not to create a separate table out of the current table.

      But to create a measure. But is this even possible?

       

      Nice tip tho, maybe I can get it working with that, if there is no resolution for the measure way.

       

       

      Cheers,

      Niko

      • nisuomi's avatar
        nisuomi
        Icon for Resolver I rankResolver I
        7 years ago

        Hi PattemManohar,

         

        With this 

         

        Last Balance =
        VAR _MaxDate = MAXX(ALL(Table1[vuosikk]);Table1[vuosikk])
        VAR _Result = IF(FIRSTNONBLANK(Table1[vuosikk];1)=_MaxDate ; sum(Table1[peruslaskukaava]);BLANK())
        RETURN _Result

         

        I got it working, that it shows only for the latest yearmonth value. But in this option, I have to click from the table "rows" dimension "show items with no value" to show it the way I want it to be seen.

         

        vuosi vuosikk Last Balance
        2018 201803 390

         

        vuosi vuosikk Last Balance
        2017 201701 (Blank)
        2017 201711 (Blank)
        2017 201712 (Blank)

        2018 201801 (Blank)

        2018 201802 (Blank)

        2018 201803 390

         

         

        Thanks for your input tho'! It got my brains rolling and was helpful for me :) If you have any other solution, please, share!

         

         

         

        Cheers,
        Niko