Forum Discussion
Calculate dates and counts based on entered date
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
I searched the forum and found another posting that answers my question:
Thank you everyone!
6 Replies
- parry2k
Super User
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.
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
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.
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.
