Calculate dates and counts based on entered date

[Anonymous]

Hi everyone -

I'm trying to convert the following calculated fields from Tableau to PowerBI. 

I have a table with a column of report numbers and a column of the date they were created.  Whoever created the Tableau report used a date parameter for "Start Date" that allowed a user to type in a random date and it would sum the number of reports in the third calculated field above.

What is the best way to do this?  I created a separate date table, but I'm not sure how to use it in this case.  I'm not clear if I should use a parameter or a slicer or if that is even the right approach.

Thanks in advance for your help!

Joseph

[Terrance]

The COUNTIFS function is built to count cells that meet multiple criteria. In this case, we need to provide two criteria: one criterion for the earlier date and one for the later date. We supply the named range dates (C5: C11) for both criteria.

To construct each date, we use the DATE function:

DATE(E5,1,1) // build first day of year

DATE(E5,12,31) // build last day of year

The DATE function makes it easy to build dates based on year, month, and day arguments that are either hard-coded or supplied as cell references. In the example, month and day are hard-coded, and we get a year from column E.

Note: the operators ">=" and "<=" must be entered as text and surrounded by double-quotes. This means we must use concatenation (&) to join each operator to each date.

[parry2k]

@Anonymous best would be if you post sample data and the expected output, it will help to provide the solution.

Please share data in excel or share pbix file using onedrive/google drive to get your answer quickly. Remove any sensitive information before sharing.

[Anonymous]

@parry2k 

Sure thing!  Thanks.  Please see link below.

https://www.dropbox.com/s/er31j2jvevdmb8e/TEST.pbix?dl=0

The output (if the user selects a start date of 4/9/2020) should be a count of around 8.  

[Anonymous]

@Anonymous 

I understand it as you want count reports within the date range of that slicer. Just try Countrow on the conditionalreport table:

Measure = COUNTROWS(CONDITIONREPORT)

 I get 149 for 4/9/2020, but 8 for 21/9/2020.

Correct me if you want to count others fields or anything I misunderstood.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

I searched the forum and found another posting that answers my question:

https://community.powerbi.com/t5/Desktop/How-to-calculate-a-12-hr-average-from-6pm-to-6am/td-p/66031...

Thank you everyone!

[Anonymous]

Hi V-pazhen-msft,

I think I need a more robust solution in this case.  Below is the operation that should occur:

The Report Date field in my table is a Date/Time field (4/24/2020 12:00:00 AM).

I would like a way for the user to be able to select a date/time value (like 4/9/2020 12:00:00 PM) from a parameter box, then Power BI will accept that date as the "Start Date."

Then, there should be a measure that calculates the date/time 24 hours from the "Start Date" parameter (for example: 4/10/2020 12:00:00 PM).  This would be the calculated "End Date."

Then a measure would also subtract 12 hours from the "End Date" (for example: 4/9/2020 12:00:00 AM).

Finally, there would be a measure that compares each Report Date in my table against those calculated fields to get how many report numbers were generated during the first shift of work for that day (like this: IF(AND(REPORTDATE >= "Start Date", REPORTDATE <= DATEADD("End Date", -12, HOUR)), 1, 0)

Is this possible?  How can I provide the user a date slicer and calculate this information from that slicer?