Substract Value: FirstDayofFollowingMonth - FirstDayofMonth

[Anonymous]

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. Everyday one more value is added.

Date	Value
01.01.2019	26.000
[...]	[... ]
01.02.2019	34.000
[...]	[...]
Date from Yesterday	Value from Yesterday

The Measure i wanna calculate is the following (can only be calculated for finished months in the past): 

(Value on the first day of the following month) - (Value on the first day of the month) = Value for Month X

Example for the table above:

Value for January: (34.000-26.000)=8000

Is there a way to solve this with DAX? I would be very happy if it is solvable WITHOUT editing the query since i have a live connection.

Thanks in advance and kind regards, mx

[v-juanli-msft]

Hi @Anonymous 

Create measures

Value on the first day of the following month = CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('date'),DATEDIFF([Date],MAX([Date]),MONTH)=-1&&[DAY]=1))

Value on the first day of the month = CALCULATE(SUM('Table'[Value]),FILTER('date','date'[DAY]=1))

Value for Month X = [Value on the first day of the following month]-[Value on the first day of the month]

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]

Hi @v-juanli-msft

Thanks for your suggestion. When i try to adapt your formulas to my data it doesnt work. 

FILTER(ALLSELECTED('date' <- is that the "Date" column out of the date hierarchy in your screenshot? Or which kind of date did you use there?

Do you have the pbi-file of your example and could maybe upload it?

Thanks and kind regards, Max 

[v-juanli-msft · Accepted solution]

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]

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]

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..