Compare number with previous periods from earlier years WITHOUT an actual date field

[casperholtfarve]

Hi everyone,

I am not sure whether or how this is possible, but I hope someone can clarify.

I have a data model containing some numbers (lets call the sales).

And then a table containing the following: Year, Quarter number, Month number and year_month number (fx. 202001).

I do NOT have a specific date available.

I can show the "sales" like this:

And it continues to 202207. 

What I would like to accomplish is the following:

I would like to sum up all the "sales" for the current year:

So in this example it is 67.477.

Then i would like to showcase the "sales" for the same months for the previous years (2018, 2019, 2020 and 2021). This means the sum of 201801 - 201807, 201901 - 201907, etc.

I know it should probably be easy with bultin functionality in Power BI if I have registered dates. But since I do not, is there another way to accomplish this?

Also I would like it to be dynamic. Meaning, that once we have passed august (month 😎 - hence 1. of september, I would like it to show 202201 - 202208, and the corresponding values for previous years as well.

Hope it makes sense and that somebody got a fix 🙂

Thanks in advance.

/Casper.  

[casperholtfarve]

Comparing the full sales of the year to the sales for the first 7 months each year 🙂
And I have verified it as well with the rows for each month.

Thanks for the assist @v-jianboli-msft 

[casperholtfarve · Accepted solution]

I think I found the solution after a little trial and error:

Measure =

VAR lastMonth =

MONTH(

TODAY()

) -1

RETURN

SUMX(

FILTER(

ALL('Date table');

'Date table'[month_number] <= lastMonth &&

'Date table'[year] = MAX('Date table'[year])

);

[Sales]

)

[Sales] was an existing standard measure summing the sales.

[v-jianboli-msft]

Hi @casperholtfarve ,

Please try:

Measure 2 = 
var _a = CALCULATE(MAX('Table'[Year_Month]),ALL('Table'))
var _b = VALUE( MAX('Table'[Year])&RIGHT(_a,2))
return SUMX(FILTER(ALL('Table'),[Year_Month]<=_b&&[Year]=MAX('Table'[Year])),[Sales])

Output:

Best Regards,

Jianbo Li

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

[casperholtfarve]

Okay the 2 tables are not an issue.

But another issue has occured.

It seems like, that the table with the year_month, actually contains all the year_month even though we have not reached the point yet.

Meaning for 2022 the table contains all the month:

202201
202202

202203

202204

202205

202206

202207

202208

202209

202210

202211

202212

This messes up the measure above as the previous years then gets ALL the data from all the years.
Is it possible somehow NOT to use the MAX function but instead a function that looks at todays month number -1? Then we will always only look at all the previous months of the year from whatever month you ar ecurrently in?

[casperholtfarve]

Thanks a lot.
It looks very promissing. But what if sales and years_month (and years) values are stored in to seperate tables?

Is it still possible? The two tables are obviously connected. And I guess this should modify the SUMX() if the measure?

Thanks in advance.

[lukiz84]

Can you show the source table? Just some dummy rows?

[casperholtfarve]

Hi lukiz84,

These are the 2 tables:

"Date"-table (without acutal dates):

It is developed in a Cube at my work with many more tables. 

The "Sales" numbers are a measure that sums up the numbers.

Put together it gives this (like I also snapped in the initial post):