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.

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    v-juanli-msft
    Community Support
    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.