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.
I've got an issue that I just can't figure out the best dax for.
I am needing to be able to count rows backwards until it no longer fits the pattern( -7 days)
We take a snapshot of a live report once a week. There are many other columns that make this thing a giant mess and the data sources is only reliable on these three columns for what I am needing to do.
If a person is active, they will show up in the report by having a row with data. If they weren't active, they won't have a row. So we have a column with names, a column with dates, adn then the third column is just to flag if that column is the latest one(it is Aug 14 for me right now). We can see that John is not in the current week.
What I am wanting to do is look at those users in the current week, and then how many weeks back they show up. So with Joe, we should see 3, even though he is on the list 5 times.
Solved! Go to Solution.
Here is one way to do this, assuming your data are in a table called "Data" (replace with your actual table name).
1. Make a Calendar table with an expression like this
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here is one way to do this, assuming your data are in a table called "Data" (replace with your actual table name).
1. Make a Calendar table with an expression like this
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Mahoneypat, It works with one small caveat. I had to remove all 2019 data, which for this report is just fine. But when we roll to a new year, how do we keep the week number going up rather than resetting back to 1 in the calendar table?
By the way, took me a couple read overs. Making the new date table is exactly what I never, ever would have thought of. Genius, thanks.
you want this in DAX? As a calculated column?
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 |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |