Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi all,
I am trying to calculate the percentage of days a group of 20 stores is open in Power BI so I can automate the process. I have the data in the current format:
Date | Store number | Closed |
01-01-2020 | 12 | 1 |
01-01-2020 | 7 | 1 |
03-01-2020 | 3 | 1 |
04-01-2020 | 12 | 1 |
07-01-2020 | 4 | 1 |
07-01-2020 | 1 | 1 |
08-01-2020 | 17 | 1 |
The calculation I basically want to make: Sum of 'Closed' / (amount of stores*amount of days in selected time frame) * 100, but I am struggling a bit with how to make that work in Power BI.
Many thanks,
E_E
Solved! Go to Solution.
I have a potential workaround but it's a bit rough and I'm sure others can improve it.
Create a Date Table (I'll call this one Date Table) and populate a column with Dates, form a relationship with the date in that to your main table with the closed results from your original post (I'll call this one Table in this example)
Add the following Columns to Date Table in DAX.
Total Storage = 20
If you decide to put these into a visual. Use the columns from Date Table to do so.
Thank you very much.
The problem with a table with 'Closed' is that there are days there is no store closed and these days won't be counted in the percentage if I create such table.
Does your data source record those days with 0 closed? Or does it only generate an entry if there is at least one?
The latter indeed, basically as in the table above. That's where I get stuck mostly.
I have a potential workaround but it's a bit rough and I'm sure others can improve it.
Create a Date Table (I'll call this one Date Table) and populate a column with Dates, form a relationship with the date in that to your main table with the closed results from your original post (I'll call this one Table in this example)
Add the following Columns to Date Table in DAX.
Total Storage = 20
If you decide to put these into a visual. Use the columns from Date Table to do so.
@Anonymous , Try a measure like
new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = mainx(allselected(Date),Date[Date])
return
divide( calculate( sum(Table[Closed]), filter('Table', 'Table'[Date] >=_min && 'Table'[Date] <=_max)) , DistinctCOUNT(Store[StoreNumber]) * datediff(_min,_max,day))
Assumed there is store master
Thank you very much.
I get some errors with the part 'allselected(Date)', where it says 'parameter isn't the correct type' & cannot find '[Date]' in the 'Date[Date])' part,
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
130 | |
110 | |
93 | |
70 | |
67 |