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,
I have a quick measure to calculate a rolling average. It was working last month, but now in January, the Card visual shows a 'Blank' value instead of the actual value. When I place the measure in a table, it calculates and displays the value, but in the Card visual, it says blank.
-------------------------------------------------------------------------------------------------------------------------------------------
The measure for that is
Because I pulled the data in by month for those queries, and my dates table is by day. It's tied to it so it matches the 1/1/2021 in the month field to the 1/1/2021 dates table 'dateValue'.
I can change the date/time to date, but if I do it on one of the tables, it breaks my measures. Not entirely sure why without doing a lot more work.
I did some checking, and if I set my 'Dates' table to end and not update past 12/31/2021, then my 'Blank' measure works again. It's only once I let the Dates update to 1/1/2022.
Hi @MageVortex
Is your date table formulated using DAX?
If so, how did you formulated the first and last date of the Date table? You may need to update the end date of your date table (e.g. change the end date to be in the future).
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
I went through the process of creating a Power BI DAX based
As good as Power BI is, setting up a calendar in that manner unfortunately will not work very effectively unless relationships and relevance have been established. Instead of working on Dates, Calendars, etc., can you tell me what is the outcome that you are trying to achieve? By this, I mean, what is "roll6SupTo no ReOrg" trying to calculate?
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
The total/overall average of the 6 month rolling average of the Supervisor turnover excluding a certain segment of those supervisors. As the original image shows, it still calculates it, but now it won't display in the KPI box.
This is what it looks like pre 2022
This is it now
Again, if I change the date range to be before 1/1/2022, it works, but once I include 2022, it no longer generates that total.
The date table is a SQL data pull from a date table we maintain which includes a cornicopia of additional potential data points around each of those dates. For instance, markers for if 1/5/2022 is a weekend day or weekday, or if it is teh first monday of the month, first weekend day of the month, or has a holiday on it, etc etc.
As to changing the end date of the table to go into the future, I can't understand why that could be the issue as it worked perfectly fine not going into the future in 2021. When I change my date query to stop on 12-31-2021, the measure works perfectly fine, but when I then let it go into 2022, it immediately stops working and goes Blank. This didn't happen at any point last year when it was the beginning or middle of the month.
I've been working on creating a DAX based calendar and creating a hierarchy, but having to manually create it is annoying as it automatically created the hierarchy for me with my sql query based dates table, and when I try to use this custom made DAX based calendar, instead of whoing all of the months going back historically, it only shows the last 6 months....sigh....each attempt creates 5 more questions to solve.
Hi @MageVortex
How have you structured your date table? A lot of times, when a date table is first created, manual inputs are used on the start and end dates. I recommend checking the end date to ensure it has rolled over into 2022.
Hope this helps.
Theo 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
It is set up the same way, goes out +60 days from todays present day (pulls from a table that goes out a few years) so the set up is consistent. I made sure the set up was consistent.
That's good to hear! With regard to your main measure, what is your [Sup Total] value? It is used in your current measure in the first line: "if(CALCULATE([Sup Total])=0,BLANK()"
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Hi @MageVortex
Okay, I think it may have to do with the current structure of your model and the relationships between your fact table and date table. Are you able to send me a screenshot of the Model view screen that shows the relationships?
Thanks heaps mate.
Theo 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
This may confuse more than help when I draw the arrows to show the connections
@MageVortex - why does the fact tables have "Month" instead of date? I noticed in the earlier data you provided, the Month column was a "Date / Time" format. Can you change this to Date format only or is this like that for a reason?
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |