Forum Discussion

totamum's avatar
totamum
Helper I
1 year ago
Solved

Create Card Showing Latest Value

I'm trying to create a Card which will display the latest Week on Week percentage that I have calculated.  My table is below.  The columns are measures based on other data.  I would like to show the latest value in the card, which is 188%.  

 

 

 

I tried the DAX formula below following a post with a similar request but I got a Plaeholder error.  I wasn't able to put the WOW% after SUM, it didn't come up for me to select, clearly I've not done something quite right

 

Latest WoW % = VAR endDate = LASTDATE ('Cumulative Users'[Week])
RETURN
CALCULATE(
    SUM('Cumulative Users'[Week]),'Cumulative Users'[WoW %]
    = endDate
)

 

Any help would be much appreciated

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi, 
    I am not 100% sure how your semantic model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

    users: = 
SUM( users[users] )

     

    OFFSET function (DAX) - DAX | Microsoft Learn

     

    Previous week users: = 
IF (
    NOT ISBLANK ( [users:] ),
    CALCULATE (
        [users:],
        OFFSET ( -1, ALL ( period[week] ), ORDERBY ( period[week], ASC ) )
    )
)

     

    WoW %: = 
DIVIDE([users:]-[Previous week users:], [Previous week users:])

     

    Latest WoW%: = 
VAR _t =
    FILTER (
        ADDCOLUMNS ( ALL ( period[week] ), "@wow%", [WoW %:] ),
        [@wow%] <> BLANK ()
    )
VAR _latestweeknumber =
    MAXX ( _t, period[week] )
RETURN
    CALCULATE ( [WoW %:], KEEPFILTERS ( period[week] = _latestweeknumber ) )

     

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    Hi, 
    I am not 100% sure how your semantic model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

    users: = 
SUM( users[users] )

     

    OFFSET function (DAX) - DAX | Microsoft Learn

     

    Previous week users: = 
IF (
    NOT ISBLANK ( [users:] ),
    CALCULATE (
        [users:],
        OFFSET ( -1, ALL ( period[week] ), ORDERBY ( period[week], ASC ) )
    )
)

     

    WoW %: = 
DIVIDE([users:]-[Previous week users:], [Previous week users:])

     

    Latest WoW%: = 
VAR _t =
    FILTER (
        ADDCOLUMNS ( ALL ( period[week] ), "@wow%", [WoW %:] ),
        [@wow%] <> BLANK ()
    )
VAR _latestweeknumber =
    MAXX ( _t, period[week] )
RETURN
    CALCULATE ( [WoW %:], KEEPFILTERS ( period[week] = _latestweeknumber ) )

     

  • POSPOS's avatar
    POSPOS
    Post Partisan
    1 year ago

    totamum :
    Step 1: Create a calculated column

    Column = IF('Table'[Week]=MAX('Table'[Week]),"Max")

     


    Step 2: Restrict the card with the calculated column to "Max". This will show the latest week value.

     


    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Your solution is so great Jihwan_Kim and POSPOS , It's worked !

    Hi, totamum 

    I wish you all the best. Previously, Super user Jihwan_Kim and POSPOS provided a solution to help you solve the problem. Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
    If you've already resolved the issue, you can mark the helpful reply as a "solution" so others know that the question has been answered and help other people in the community. Thank you again for your cooperation!
    If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

     

    Best Regards

    Jianpeng Li

  • totamum's avatar
    totamum
    Helper I
    1 year ago

    Thank you very much that did it!  Jihwan_Kim I'm not sure whether you able to summarise what the formula means/what it is doing.  I need to learn DAX in any case but thank you ğŸ™‚

     

    POSPOS Thank you also!  My only issue is that my week numbers aren't working by calendar year so at the moment I've got 17th January as week 52 and not the latest week but otherwise nice and simple and worked.  Thanks