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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
android1
Post Patron
Post Patron

Count using IF &&

Hi,

 

I have a column Hrs Sold (format is decimal number).  Each Hrs Sold has a start time and end time (format is Time)

 

I am trying to count the total Hrs Sold between 8am - 10pm.

 

I am using the calculated column 8AM-10PM = IF(HOUR(vw_PivotChargesFrontPage[Visits_StartTime]) >= 8 && HOUR(vw_PivotChargesFrontPage[Visits_EndTime] <=21 && MINUTE(vw_PivotChargesFrontPage[Visits_EndTime]) <=59),TRUE(),FALSE())

 

but getting False for everything.

 

2018-07-09 12_47_23-Invitation to Tender Call Analysis - Power BI Desktop.jpg

 

 

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

I think you should use measure instead - unless you want the nr of hours sold repeated for every hour in a day
this syntax should work for a measure:

8AM-10PM = 
CALCULATE(
    SUM(vw_PivotChargesFrontPage[Hrs Sold]),
    vw_PivotChargesFrontPage[Visits_StartTime]>=TIME(8,0,0),
    vw_PivotChargesFrontPage[Visits_EndTime]<=TIME(22,0,0)
    )

alternatively you can just show sum of hours sold and filter the time range you need in the visual



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

2 REPLIES 2
Stachu
Community Champion
Community Champion

I think you should use measure instead - unless you want the nr of hours sold repeated for every hour in a day
this syntax should work for a measure:

8AM-10PM = 
CALCULATE(
    SUM(vw_PivotChargesFrontPage[Hrs Sold]),
    vw_PivotChargesFrontPage[Visits_StartTime]>=TIME(8,0,0),
    vw_PivotChargesFrontPage[Visits_EndTime]<=TIME(22,0,0)
    )

alternatively you can just show sum of hours sold and filter the time range you need in the visual



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Yeah, that does the job. Thanks for your help.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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