Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
Anonymous
Not applicable

Multiply by amount of days in selected range

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:

DateStore numberClosed
01-01-2020    121
01-01-2020    71
03-01-2020    31
04-01-2020    121
07-01-2020    41
07-01-2020    11
08-01-2020    171

 

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

1 ACCEPTED 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

Closed = CALCULATE(SUM('Table'[Closed]),'Table'[Date])
ClosedCalc = IF(ISBLANK('Date Table'[Closed]),0,('Date Table'[Closed]))
ClosedPC = 'Date Table'[ClosedCalc]/'Date Table'[Total Storage]

That should give you your totals to be able to do a date range calculation if you use either ClosedCalc or ClosedPC. 

I'm certain that Closed, ClosedCalc and ClosedPC should be able to be merged into a single Custom Column but my brain eludes me on that, others may know though!


If you decide to put these into a visual. Use the columns from Date Table to do so. 


Please note - I am not affiliated with Microsoft, I'm just an end user like yourself.
Just a regular guy doin' Data Science.

If my post has helped you, please don't forget to thumbs up or click "Accept as solution" if it solved your problem!

View solution in original post

7 REPLIES 7
AlexanderPrime
Solution Supplier
Solution Supplier

Perhaps make a measure with the following code:
 
Closed PC = (SUM('Table'[Closed])/20)
 
Then insert this into a table with just Date and Closed present. Then set Closed PC to a "Percentage" data type. I'd recommend against multiplying the results by 100 where possible as Power BI treats percentages as a decimal, with 1 equalling 100%. 
 

 


Please note - I am not affiliated with Microsoft, I'm just an end user like yourself.
Just a regular guy doin' Data Science.

If my post has helped you, please don't forget to thumbs up or click "Accept as solution" if it solved your problem!
Anonymous
Not applicable

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?


Please note - I am not affiliated with Microsoft, I'm just an end user like yourself.
Just a regular guy doin' Data Science.

If my post has helped you, please don't forget to thumbs up or click "Accept as solution" if it solved your problem!
Anonymous
Not applicable

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

Closed = CALCULATE(SUM('Table'[Closed]),'Table'[Date])
ClosedCalc = IF(ISBLANK('Date Table'[Closed]),0,('Date Table'[Closed]))
ClosedPC = 'Date Table'[ClosedCalc]/'Date Table'[Total Storage]

That should give you your totals to be able to do a date range calculation if you use either ClosedCalc or ClosedPC. 

I'm certain that Closed, ClosedCalc and ClosedPC should be able to be merged into a single Custom Column but my brain eludes me on that, others may know though!


If you decide to put these into a visual. Use the columns from Date Table to do so. 


Please note - I am not affiliated with Microsoft, I'm just an end user like yourself.
Just a regular guy doin' Data Science.

If my post has helped you, please don't forget to thumbs up or click "Accept as solution" if it solved your problem!
amitchandak
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.