topic Re: Dax - Show the value of the last month in DAX Commands and Tips

Dax - Show the value of the last month

NicolPuglia — Fri, 24 Apr 2020 12:26:55 GMT

Hi,

I have a problem that I can't solve with dax.

I have this widget to select years.

When I select 2019, I would like it to show me only the data of the range December 1 to December 31, 2019 and not the sum of all the months.

While when I select the current year it shows me the end of the previous month. Example: We are in April, I would like you to show me the data from 1 March 2020 to 31 March 2020.

I have a table that contains the dates, and especially the End_Dates of each year.

Thank you so much for the support!

Nicola

Re: Dax - Show the value of the last month

Greg_Deckler — Fri, 24 Apr 2020 12:38:28 GMT

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: Dax - Show the value of the last month

NicolPuglia — Fri, 24 Apr 2020 12:55:57 GMT

Hi,

I have not found the answer I was looking for.

Thanks

Re: Dax - Show the value of the last month

Anonymous — Fri, 24 Apr 2020 13:11:08 GMT

If possible please attacha demo dataset or original dataset so that we can work and solve your query.

Thanks

Kaushlendra Mishra

www.powerbitalks.com

Re: Dax - Show the value of the last month

Anonymous — Fri, 24 Apr 2020 13:48:02 GMT

// Assuming that you do have a proper // Calendar in your model... [Your Measure] = var __currentlyVisibleYear = SELECTEDVALUE( Calendar[Year] ) var __TODAY = TODAY() var __todaysYear = YEAR( __TODAY ) var __resultWhenBefore = var __period = DATESINPERIOD( Calendar[Date], DATE( __currentlyVisibleYear, 12, 31 ), -1, month ) var __result = CALCULATE( [Measure You Want To Display], __period ) return __result var __resultWhenIn = var __period = CALCULATETABLE( PREVIOUSMONTH( Calendar[Date] ), Calendar[Date] = __TODAY ) var __result = CALCULATE( [Measure You Want To Display], __period ) return __result var __result = switch( TRUE(), __currentlyVisibleYear < __todaysYear, __resultWhenBefore, __currentlyVisibleYear = __todaysYear, __resultWhenIn ) return __result

Best

Re: Dax - Show the value of the last month

NicolPuglia — Fri, 24 Apr 2020 14:15:11 GMT

Hi ,

thanks all!

I have this table:

Years: My calendar

Value: Value

- Value:

- Years:

In 2020 I would like it to show the value from the beginning and end of the last month.

Example: We are in April 2020, I would like the data to be seen from March 1 to March 31 in this case 15.

I select the year 2019, I would like it to show the data from 1 December to 31 December 2019.

By selecting in the widget.

Thanks!!

Re: Dax - Show the value of the last month

Anonymous — Fri, 24 Apr 2020 14:25:52 GMT

I have written the measure above. It'll be working if you make sure that the Years table is a proper calendar/date table in the model. As it should be.

Best
D

Re: Dax - Show the value of the last month

NicolPuglia — Fri, 24 Apr 2020 14:37:37 GMT

Hi,

thanks a lot for the code, i'm compiling it.

One question: where did you write '[Measure You Want To Display]', how can I fill it out?

Thank!!!!

Re: Dax - Show the value of the last month

Anonymous — Fri, 24 Apr 2020 14:59:20 GMT

This is a placeholder for your measure. Your base measure for which you want to do the time transformation/calculation.

Best
D

Re: Dax - Show the value of the last month

NicolPuglia — Fri, 24 Apr 2020 15:13:43 GMT

Hi, just that I'm really having trouble writing the time calculation in the CALCULATE. Based on those two tables or if you have an example how can I do it?

really thank you very much

Re: Dax - Show the value of the last month

Anonymous — Fri, 24 Apr 2020 23:22:21 GMT

Please inspect the attached file.

Best

Re: Dax - Show the value of the last month

NicolPuglia — Mon, 27 Apr 2020 07:25:29 GMT

Hi,

when i open the pbix , generate this error

Error Message:
Object reference not set to an instance of an object.

Thanks!!!

Re: Dax - Show the value of the last month

Anonymous — Mon, 27 Apr 2020 08:47:23 GMT

Update your Power BI Desktop to the latest version.

Best
D

Re: Dax - Show the value of the last month

NicolPuglia — Mon, 27 Apr 2020 21:08:00 GMT

Very Thanks!!!

Nicola!