Forum Discussion

New Member

3 years ago

Status for every Day Power Query

Hi Power BI Experts, I need an advice how to deal with this kind of data. I would like to show how often a vehicle is in maintannce or rented etc. in a timeframe. But my Problem is that I have St...

Tips and Tricks

Tutorial Requests

New Member

3 years ago

Short Update: The Data looks like this:

Memorable Member

3 years ago

"how often" is this a number of times (regardless of how long) or a number of days (may be as a % to the total days in the timeframe)?

Also do you expect to do it in DAX or using Power Query?

Cheers,

John

Hasan29
New Member
3 years ago
Hi jbwtb,

Thank you for your answer. I would like to show the Number of days. And I would need a % of the Number of Days. For example August Number of Days / 31 Days.

The best or easiest way. I dont know what is easier dax or query.

Regards,
- jbwtp
  Memorable Member
  3 years ago
  Hi Hasan29
  
  Assuming that we have two tables like this:
  Dates
  etc.
  
  Items
  
  This is the measure to calcualte % on minatenance, you can create other using this as a template:
  
  OnMaintenance = sumx(filter(Items, Items[Status] = "maintenance"), max(0,1+DATEDIFF(max(Items[Start Date], MINX(Dates, [Date])), min(Items[End Date], MAXX(Dates, [Date])), DAY))) / (DATEDIFF(STARTOFMONTH(Dates[Date]), ENDOFMONTH(Dates[Date]), DAY)+1)
  
  Note that the tables are not linked to each other (i.e. no arrows between them on the relationships view).
  
  This is the output view:
  
  P.S. Sorry need to clarify: Month is the date foeld from dates (just grouped):
  
  Kind regards,
  John
  - Hasan29
    New Member
    3 years ago
    Hi,
    
    thank you for your answer. The formular does not work for me.
    After "maintenance") I cant continue the Code with "," (syntax is incorrect). I have a Date table just like that.
    
    Thank you.
    
    Regards,
    Hasan

Related content