Forum Discussion
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. 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
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.
5 Replies
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
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..
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.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
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.