Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@Hinnax and here is the measure:
Measure_Dec+Jan =
CALCULATE(
[Measure_1],
FILTER (
ALL ( 'Date'[Date] ),
'Date'[Date] <= MAX ( 'Date'[Date] ) )
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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 and here is the measure:
Measure_Dec+Jan =
CALCULATE(
[Measure_1],
FILTER (
ALL ( 'Date'[Date] ),
'Date'[Date] <= MAX ( 'Date'[Date] ) )
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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 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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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.
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |