Creating a Measure to show data depending on the date

LowerB — Thu, 14 Oct 2021 13:54:32 GMT

Hello all,

this is my first post and I'm fairly new to Power BI, so if I do something wrong please let me know.

Situation:

I have a matrix, where I display costs and revenue for each month of the year. This works great so far when the Values come from one of my tables.

Issue:

I would like to display different values for different time frames depending on the current date (month) e.g.:

This month (October) I would like to display the data as the following:

Values from Source 1: Up until the previous month (in this example: up until September)

Values from Source 2: Current month (in this example: October)

Values from Source 3: +1 & +2 months from current month (in this example: November & December)

Values from Source 4: +3 & +4 & +5 months from current month (in this example: Jan, Feb, March)

Values from Source 5: +6 and following (in this example: April, May, June...etc.)

I have tried various measures but it either doesnt show all the correct data or it clips off the data for each year (taking the same "cut-off" for every year, even in the past, when the past should only display values from Source 1.

I haven't found a proper solution in any other posts and when I creatively tried to combine different solutions it didn't give me the result I was looking for.

I hope anyone out there is able to help me somehow or stirr me in the right directions as I think I am mainly blocking myself right now because I'm frustrated.

Thanks in advance for any help, even if it is telling me I'm in the wrong place and directing me on where to go with this issue! 🙂

Re: Creating a Measure to show data depending on the date

amitchandak — Thu, 14 Oct 2021 14:57:41 GMT

LowerB , You need break down into few problem and try to solve it.

You need to use a common date table and join it with date of all tbales then

example of month data

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]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))

Till Date last month =

var _max = eomonth(maxx(allselected('Date'), 'Date'[date]) , -1)

return

CALCULATE(SUM(Sales[Sales Amount]), filter(all('Date'), 'Date'[date] <=_max) )

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

next three

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),3,MONTH))

last three

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),13,MONTH))

last 3 before 3

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-3),13,MONTH))

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.

topic Re: Creating a Measure to show data depending on the date in DAX Commands and Tips

Creating a Measure to show data depending on the date

Re: Creating a Measure to show data depending on the date