Forum Discussion

Applicable88's avatar
Applicable88
Impactful Individual
4 years ago
Solved

Sum values from current months datekey

Hello,

 

I have following table here with YearMonth Key. The Accounting month is slightly different then the calendar month, since the last working day of the month belongs to the next month :

 

Date  Values AccountingMonth
28.07.2022 10 202207 
29.07.2022 10 202208 
30.07.2022 0 202208 
31.07.2022 0 202208 
01.08.2022 20 202208 
02.08.2022 20 202208 

 

I need to measures:

1. At any given time I want to sum the values of the current accounting month.

 

2. At any given time sum the values of the last accounting month. 

 

So far when I use an example with the Filter function, I ended up only getting the row of today, but not all the rows of the wanted accounting month. 

 

Thank you very much in advance. 

Best. 

 

 

Need Help
Show and Tell
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Applicable88 

     

    Create a new calendar table.

    Table:

    Date = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))

    Column:

    Month = MONTH([Date])
    Weekday = WEEKDAY([Date],2)
    Maxworkday = 
CALCULATE(MAX('Date'[Date]),FILTER('Date',[Month]=EARLIER('Date'[Month])&&[Weekday]=5))
    AccountingMonth = Year([Date])&"0"&IF([Date]>=[Maxworkday],[Month]+1,[Month])

    Measure:

    Sum =
CALCULATE (
    SUM ( 'Table'[Values] ),
    FILTER (
        ALL ( 'Date' ),
        [AccountingMonth] = SELECTEDVALUE ( 'Date'[AccountingMonth] )
    )
)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi, Applicable88 

     

    Create a new calendar table.

    Table:

    Date = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))

    Column:

    Month = MONTH([Date])
    Weekday = WEEKDAY([Date],2)
    Maxworkday = 
CALCULATE(MAX('Date'[Date]),FILTER('Date',[Month]=EARLIER('Date'[Month])&&[Weekday]=5))
    AccountingMonth = Year([Date])&"0"&IF([Date]>=[Maxworkday],[Month]+1,[Month])

    Measure:

    Sum =
CALCULATE (
    SUM ( 'Table'[Values] ),
    FILTER (
        ALL ( 'Date' ),
        [AccountingMonth] = SELECTEDVALUE ( 'Date'[AccountingMonth] )
    )
)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Applicable88 , Best to have date table with dates and accounting month and create rank on accounting month

     

    Month Rank = RANKX(all('Date'),'Date'[Accounting Month],,ASC,Dense)

     

    if date is selected

    This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
    Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

     

    Based on today

     

    This Month =

    var _max = maxx(filter(ALL('Date'), 'Date'[Date] = today() ) , 'Date'[Month Rank] )

    return

    CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]= _max ))

     


    Last Month =

    var _max = maxx(filter(ALL('Date'), 'Date'[Date] = today() ) , 'Date'[Month Rank] )

    return

    CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=_max -1))

    • Applicable88's avatar
      Applicable88
      Impactful Individual
      4 years ago

      sorry amitchandak, 

      I forgot to mention that the Datekey is in another table connect with the table above over the date column.

      Using maxx month date would get me the highes date available in the mastercalendar table, which of course would be wrong.