HELP!!!! automate Period

[486kyoko]

Power BI users out there!

I need your help. this looks-not-so-complex but i cannot find the way to resolve getting me nut..

i have built monthly actual to budget dashboard for my board members.

for the current month, the formula is as follows;

Current Month =

CALCULATE(

    SUM('A_OPEX'[Actual]),

    'RTable_Period'[Period] IN { DATE(2020,10,01)},

    ALL('RTable_Period'[Order])

)

every first week of each month, i go and replace "10" october to next month "11"

then next month, 11 to 12.... 

how can i automate that it picks up the 1st day of previous month?

i got the TODAY() function but it wont bring me DATE(2020,10,01) of course?

then i got 

LASTMONTH = LASTDATE('9 Series Actual'[Period]) which ends me result of 2020,10,01

then use 

Current Month =

CALCULATE(

    SUM('A_OPEX'[Actual]),

    'RTable_Period'[Period] IN { [LASTMONTH]},

    ALL('RTable_Period'[Order])

)

but it gives me error.

Please help.

[v-deddai1-msft · Accepted solution]

Hi @486kyoko ,

Would you please try to use the following measure:

Current Month =
CALCULATE (
    SUM ( 'A_OPEX'[Actual] ),
    FILTER (
        'RTable_Period',
        'RTable_Period'[Period]
            = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
    ),
    ALL ( 'RTable_Period'[Order] )
)

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

Best Regards,

Dedmon Dai

[486kyoko]

Amazing...thank you! All i did was just copy and paste your formula!! this means as at today 2020 nov 27th, it take a date of today's year 2020, then month today (-1= Oct), and 1... the frst of the month!! totalling 2020,OCT,1?

[amitchandak]

@486kyoko , You can use time intelligence with date table

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.