Aggregation of LATest (LAST) value

[BMenescal]

Hi all!

I´m new on Power Bi, I am migrating from Oracle (OBIEE) to Power BI.

In Oracle Business Intelligence (OBIEE) I can generate an aggregation with SUM and LAST. Taking the last value of the month for a given fact.

I need the last value for the month of January to be displayed in the month's aggregration value.

Does anyone have any idea how to do this?

I have researched several sources and forms but I have not been successful.

thank you in advance.

Example:

[danextian]

HI @BMenescal ,

You may create a calculated column to return the value of the last date of the month without regards to whether the date is the actual last date of the month in the calendar. 

First create a calculated column to group the dates into Year and Month - something like:

Month and Year = 
FORMAT ( 'Fact'[Date], "YYYYMM" )

And then another calculated column

Is Last Date? =
CALCULATE (
    LASTDATE ( 'Fact'[Date] ),
    ALLEXCEPT ( 'Fact', 'Fact'[Month and Year] )
) = 'Fact'[Date]

You may then use this calculated column to filter your aggregations. 

[BMenescal]

Hello @danextian , thanks for you reply!

I have this problem cause i´m using DirectQuery mode.

"The FORMAT function is not allowed as part of the calculated column DAX expressions in DirectQuery models"

[amitchandak · Accepted solution]

Yes. lastdate can do that. First, prefer to have date table. And move all your month, year calculation there.

Now this lastdate takes row context. So if you select the month, it will give you month last date, If you display the date it will give you all the dates.

The second measure uses end of month and forces filter of that

Last day = CALCULATE([Sum Sales],LASTDATE('Date'[Date]))
end of month = CALCULATE([Sum Sales],filter('Date','Date'[Date]=endofmonth('Date'[Date])))

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
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[BMenescal]

Thank you so much @amitchandak ! 

It worked correctly both ways.

See below: