Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Substract Value: FirstDayofFollowingMonth - FirstDayofMonth

Hi,   I have the following scenario and not yet found a solution:   Table has a "Date" and "Value" Column. Every day in the past has a value up to yesterday - no values for today or the future. E...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    "date" here is a table called "date",

    My date table

    date =
ADDCOLUMNS (
    CALENDARAUTO (),
    "YEAR", YEAR ( [Date] ),
    "MONTH", MONTH ( [Date] ),
    "monthname", FORMAT (
        [Date],
        "mmm"
    ),
    "yy-mmm", FORMAT (
        [Date],
        "yyyy-mm"
    ),
    "DAY", DAY ( [Date] )
)

    Check my file below.

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous,

 

The following should create a measure adding the monthly total against the 1st day of each month (e.g. Total for Jan shows against the 1st Jan, Feb against 1st feb etc...). You shoud be able to change 'DailyStats' for the name of your own table.

 

It is set up to return 0 if the month has not yet ended.

 

 xMonthVal =
MAX (
    IF (
        DAY ( SUMX ( DailyStats, DailyStats[Date] ) ) = 1,
        LOOKUPVALUE (
            DailyStats[Value],
            DailyStats[Date], DATEADD ( DailyStats[Date], 1, MONTH ),
            0
        )
            - SUMX ( DailyStats, DailyStats[Value] ),
        0
    ),
    0
)

 

Cheers,

Andy

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Andy

    Thanks for your fast reply. It almost works:

     

    Example for January: What it currently does is, it gets the value of the second day in February and then gets the second value of january and substracts both. Not exactly sure why it does that since it looks like your formula looks for the Day = 1..