Time Intelligence over 2 Months

[Hinnax]

Hello everyone,

I’m pretty new to Power Bi so I‘m afraid my question is too trivial for this forum.

Before I describe the problem, let me start with my setup:

I use a date table with dates over several years. To "navigate through time" I added a few calculated colums to my date-table, which contain if statements and TRUE() / FALSE() as answer. (I assume this is the worst way to filter things, so suggestions about how to do it properly are very welcome)

My source contains -simplified- just a date and a value.

Example of my report:

Date              Value
29.12.2020    3
29.12.2020    1
04.01.2021    2
04.01.2021    5
06.01.2021    3
06.01.2021    6

(Edit: Things got messed up, quickfix)

So here is my problem:

I tried to summarize the values with the time intelligence (December, January), but Power BI keeps seperating the two months instead of summarizing them. If I use the time intelligence for January only, it works fine. (The measure for January looks similar to the ones below)

(The Values without any change are weekends or holidays. The marked value is the first entry for January. As you can see, the values get summarized for December and January separately)

My Measures look like the following examples, I just altered the names, so that you're able to read it properly:

Measure_1 = CALCULATE(SUM('report'[Value]), 'Date[Boolean_X]=TRUE(), 'Date[Boolean_Y]=TRUE() )

(This summarizes all values for the last 4 weeks)

Measure_2 = CALCULATE([Measure_1], DATESBETWEEN('Date[Date], STARTOFMONTH(LASTDATE('Date[Date])), LASTDATE('Date[Date])))
(this one is used in the visual so that i can use the forecast function)

I tried different approaches, unfortunately none of them worked. This is the latest state of the Measures

The filtering for the desired time period works fine in my other calculations.
I hope I included every info needed for an answer. If my approach is complete garbage, I would be glad about a suggestions how to do it otherwise.
Usually I prefer to inform myself befor I ask questions but this one is kind of time critical. Unfortunately I got a brain freeze after several dozen similar, but slightly different posts.

Thank you in advance.

[parry2k · Accepted solution]

@Hinnax and here is the measure:

Measure_Dec+Jan = 
CALCULATE(
[Measure_1],
FILTER ( 
ALL ( 'Date'[Date] ), 
'Date'[Date] <= MAX ( 'Date'[Date] ) ) 
) 

[Hinnax]

That's exactly what I was looking for. Thank you very much!
It seems it's the same Measure you posted 2 hours ago 😅😅 I don't know what I did wrong when I tested it.
Would it be too much to ask for, to kindly explain the mechanic behind your mesure? I really want to understand it.

[parry2k]

@Hinnax this is what you are looking for, right?

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

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

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Hinnax oh your looking for running total, here it is :

Measure_2 = 
CALCULATE([Measure_1], 
FILTER( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) 
)

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

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

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Hinnax]

I think i need some additional support.

https://drive.google.com/drive/folders/17U2De_t_F52CEuXzFvw7Dz2zJgp1Wk3F?usp=sharing

If you want me to provide the data in a different format, just let me know.
(It's a date table, a excel file as source and a .pbix)

I hope I understood the "How to provide sample data in the Power BI Forum" correctly.
Thank you in advance.

[parry2k]

@Hinnax you have provided a lot of great details but it will be easier if you share sample data and expected output. Start from there and you get into more details.

[Hinnax]

Just a quick shot:

The values for January getting summarized fine.
In the Visual for January + December the two months are summarized seperately. The red arrow in my posts marks the same point in my data. It's the beginning of January. In the Visual for January + December, I expected that the Values from January get summarized with the Values from December, without a "reset".
The calculations for January are the same in both Visuals.

I  clearly underestimated how much sample data is needed tho answer my question. Unfortunately I only could provide the original data from my company at this moment. I'll prepare better examples to post them tomorrow.