Results 1 to 2 of 2

Thread: DAX, how to last value in the period total
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2004
    Posts
    669
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default DAX, how to last value in the period total

    I have a table like this

    Country, headcount, Month
    ==================
    US, 23, May2019
    US, 24, Jun2019
    UK, 27, May 2019
    UK, 27, Jun 2019

    Now Excel pivot-table of Country as row and Month as Column, the Q2 column is showing 47 (23+24) for US.
    So I am thinking I need to use DAX instead...

    Is there a way the Q2 column be the latest value in the Quarter, ie 24 instead ?

    Thanks

  2. #2
    Board Regular
    Join Date
    Aug 2009
    Location
    Southern Finland
    Posts
    1,517
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DAX, how to last value in the period total

    If your Month is actual date you should get what you're after with something like:

    Last Day's Head Count:=CALCULATE(sum(Table1[Headcount]);FILTER('Calendar';'Calendar'[Date]=max(Table1[Date])))

    Filter using the Calendar (dimension) table instead of the Month/Date column of your fact table.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •