making a calculation dynamic

[MarcUrdang]

Good morning .. I have a calculation which I created with the Time Intelligence function. I need to be able to make the month dynamic so that each month it will go back to the beggining of the year to calculate: So where it reads 4 now then in June it must go back 5 etc without doing it manually... are you able to help out with an idea?

TEU last_m_thisyear OUT =

CALCULATE (

SUM ( 'Consol OUTBOUND'[TEU Out] ),

FILTER (

'Consol OUTBOUND',

'Consol OUTBOUND'[First Leg Load ETD]

>= DATE ( YEAR ( today() ), MONTH ( TODAY() ) - 4, 1 )

&& 'Consol OUTBOUND'[First Leg Load ETD] <= DATE ( YEAR ( TODAY() ), MONTH ( TODAY() ), DAY ( TODAY() ))

)

)

[sanimesa]

@MarcUrdang  How do you want to use this measure? You can place this measure into a table for instance where the columns are months, in which case you can easily get the context for month.

Something like:

EU last_m_thisyear OUT =

VAR curMonth = SelectedValue(Month)

RETURN CALCULATE (

SUM ( 'Consol OUTBOUND'[TEU Out] ),

FILTER (

'Consol OUTBOUND',

'Consol OUTBOUND'[First Leg Load ETD]

>= DATE ( YEAR ( today() ), MONTH ( TODAY() ) - curMonth, 1 )

&& 'Consol OUTBOUND'[First Leg Load ETD] <= DATE ( YEAR ( TODAY() ), MONTH ( TODAY() ), DAY ( TODAY() ))

)

)

[parry2k]

@MarcUrdang I think you want to sum from the start of the year and in that case use DATESYTD function

Sales YTD = 
CALCULATE ( SUM ( Table[Sales] ), DATESYTD ( DateTable[Date] ) ) 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[MarcUrdang]

thanks so much for your help thus far .. however the calc is not doing what I would expect .. perhaps if you are able to share a Teams meeting with me I could show you what I need.

But let me try explain again. I have a dataset that has a particular value column and a date pertaining to each shipment. I have a number of years worth of data. Therefore I want to show the previous year to date AS WELL AS the current year to date. That is why I used the time intelligence calcs to manage this .. but the month and year is not dynamic when using those functions. 

thanks

Marc

[MarcUrdang]

Hi .. thank you so much .. I will try it in the morning ... I basically need the calc to work for the current year AND the previous year ... woudl I be able toadapt this for 1 year back also?

thanks 
Marc

[sanimesa]

@MarcUrdang  It should not be impacted by the year. However, please look at time intelligence applications, you may not need to hard code even the year.

[parry2k]

@MarcUrdang we are over complicating the calculation if you use date dimension and in build time intelligence function, this all will be dynamic and super easy to work with.

As a best practice, add date dimension in your model and use it for and time intelligence calculations. There are many posts on how to add date dimension and below is the link to a few. Once the date dimension is added, mark it as a date table on table tools.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns