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

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.

Reply
mary_pryma
Regular Visitor

Calculate last nonblank values and show them on Time chart like cumulative total

I hope I can explain as clearly as possible.
I have a table with status changes of cars.
The machine can have 3 statuses: 0 (done), 1 (in work) or 2 (new)
Status change writes per datetime
So, I create measure how much cars have status 1 (and required categories) on and before selected date and time
For example i choose 02.06.2023 and time 4.00 am 
so from table i get 68 cars started work before and on this time
my measures
Start work before chart =
var count_d = calculate(DISTINCTCOUNTNOBLANK('Change Log Entry (status)'[lookup_car]), filter(ALL('Change Log Entry (status)'), 'Change Log Entry (status)'[date hour] <= max('Change Log Entry (status)'[date hour]) && 'Change Log Entry (status)'[Date and Time local]=[last date] && 'Change Log Entry (status)'[New Value] = "1"
&& [Розподіл авто]<>"тривалий ремонт (більше 2 діб)" &&
[Розподіл авто]<>"капітальний ремонт (більше місяца)" &&
[Розподіл авто]<>"заправка (до 1 доби)" &&
[Розподіл авто]<>"перезмінка (до 1 доби)" &&
[Розподіл авто]<>"паркінг (більше 1 доби)" &&
[Розподіл авто]<>"короткий ремонт (1-2 доби)"))
return
count_d
 
last date =
VAR _result =
    CALCULATE (
        max(  'Change Log Entry (status)'[Date and Time local]),
        REMOVEFILTERS ( 'Change Log Entry (status)' ),
        VALUES ( 'Change Log Entry (status)'[lookup_car] ),
        filter(ALLSELECTED ( 'Change Log Entry (status)'),  'Change Log Entry (status)'[Date local]< MAX (   'selected_date'[Date])
       || ('Change Log Entry (status)'[Date local]= MAX ('selected_date'[Date]) && [new Time]<= max('Hours'[new Time]))))
RETURN
    _result

 

'selected_date'[Date] and 'Hours'[new Time] haven't relationships with my table (just simple date and time tables)

 

 

mary_pryma_1-1686125738887.png

 

 

But now i need to show on chart how much cars started work on start day (from 1am because i show cars before to hour) and to end of day
it should be measure per selected date from slicer (not depend from time slicer) and chart should look like that  

mary_pryma_0-1686172539568.png


And today it should be values only to the active hour 
but for another day from slicer, it should be all day on chart

I've been thinking about this for 2 weeks now and every time my measure is incorrectly displayed on the graph

 

1 REPLY 1
lbendlin
Super User
Super User

Does your calendar table have the required hour level granularity?

 

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors