count of rows on or before dax function

[Anonymous]

Hello, 

I have three tables and these three tables are linked to a TABLE CALENDAR.

Table A [opened_date] -> calendar table [date]
Table B [close_date] -> calendar table [date]
Table C [opened_date] -> calendar table [date]

all my measures are working perfectly in a Matrix, where I can see the result of each measure through the date and drill down my problem is passing, for being able to count how many rows were open from table A until the end of a certain date .

Table A
---------------
ID                     OPENED_DATE
1                        1/01/2018
2                        15/01/2018
3                        1/02/2018
4                        16/02/2018
5                        1/03/2018
6                        17/03/2018
7                        1/04/2018
8                        15/04/2018
9                        01/01/2019
10                      15/01/2019
11                      01/02/2019
12                      16/02/2019
13                      1/03/2019
14                      17/03/2019

if I put the measure that will add up to the amount of rows that were opened every year and every month, it works, but what I intend is to add how many were open at the end of each year / month / day

for example:

if we choose to do drill down in the matrix until we see all the month of 2019, what should appear was in the month of January 2019 were opened that 10 new lines were opened but instead only tell me 2 because it was the number that opened in January , but what I really wanted was to get the count of on or before a certain date chosen.

The measure I use to calculate is this:

# of Open = CALCULATE (
COUNTROWS (PROD) + 0,
FILTER (PROD, PROD [PRIORITY] = "1 - Critical" || PROD [PRIORITY] = "2 - High"),
FILTER (PROD, PROD [U_ENVIRONMENT] <> "UAT"),
FILTER (PROD, PROD [CLOSED_AT] = BLANK ())
)

Can anyone help me ?

Best Regards,

JO

[Greg_Deckler]

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[Anonymous]

Thanks for the quick response,

but I tried to see the two suggestions that you sent me and I tried to understand, but I can not understand how I will fit those DAX functions into my DAX function because I'm still very new to DAX function.

Could someone help me?

Best Regards

[Greg_Deckler]

Can you just do a Merge query and then your table should basically be like the one in Open Ticket.s Otherwise, share your Close table like you did your Open table with the expected results.

[Anonymous · Accepted solution]

Hi, 

and thank you again for your response!

Table A

-----------

ID                      Close_Date(in type string)

1                       1/02/2018

2                       1/05/2018

3                       15/06/2018

4                       empty

5                       empty

6                       18/06/2018

7                        1/05/2018
8                        15/06/2018
9                        01/03/2019
10                     15/03/2019
11                      01/04/2019
12                      20/02/2019
13                       empty
14                       empty

My open date table is also of type string, what I did was create a calculate column that reads from the table open the text and convert to date, so I can connect to my calendar table.

To then be able to put all my measures in a MATRIX visual in which they share all the same date column.

My goal is to calculate how many rows were open at the end of each desired date, then calculate how many were open at the end of each day or at the end of each month (no close state)

For example:

JANUARY 2018 -> 2
MARCH 2018 -> 5 (not only the two that were opened in March but the totality that were open at the end of March, without a closed date in March 2018)

I use a MATRIX VISUAL not a table visual.

Can you give me a hand? Really grateful.

Best Regards,

JO