cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hinnax
Frequent Visitor

Time Intelligence over 2 Months

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)

Calc.png

(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.

1 ACCEPTED SOLUTION
parry2k
Super User III
Super User III

@Hinnax and here is the measure:

 

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

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

7 REPLIES 7
parry2k
Super User III
Super User III

@Hinnax and here is the measure:

 

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

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Hinnax
Frequent Visitor

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
Super User III
Super User III

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

 

 

image.png

 

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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User III
Super User III

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hinnax
Frequent Visitor

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
Super User III
Super User III

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hinnax
Frequent Visitor

Just a quick shot:
Calc1.png
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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.