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.
Hi,
I have data like below.
Now, I want to count how many journals were closed and how many journals were opened on a particular date.
How to do this?
Sample file:
https://drive.google.com/file/d/1EEij_gV3NohDy1YXKrpSskoRlUQbmz19/view?usp=sharing
@pawelj795 , Refer to my blog on a similar topic. You need to create a date table and join both dates to it. Active/inactive relation will be created. You can use userelation to activate one
https://www.youtube.com/watch?v=e6Y-l_JtCq4&t=304s
@amitchandak
I've done everything you wrote in your article, but it still doesn't work.
Could you tell me what's wrong?
Below my file:
https://drive.google.com/file/d/1cZWBHS07cqX62m_6vnR5X--Op0QceGyT/view?usp=sharing
Hi @pawelj795 Get rid of the relationships and create these measures:
Count of Closed Journals V2 =
CALCULATE (
DISTINCTCOUNT ( Sheet1[Journal number] ),
FILTER (
ALL ( Sheet1[Closed Date] ),
Sheet1[Closed Date] <= MAX ( 'Date'[Date] )
&& NOT ISBLANK ( Sheet1[Closed Date] )
)
)
Count of created Journals V2 =
CALCULATE (
DISTINCTCOUNT ( Sheet1[Journal number] ),
FILTER (
ALL ( Sheet1[Created date] ),
Sheet1[Created date] <= MAX ( 'Date'[Date] )
)
)
See it all at work in the attached file.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@AlB
Thanks for your help.
Unfortunately, your measure is sum journals cumulatively and this isn't my case.
Ah, you want the openings and closing only on that specific date? Easy, we just need an = instead of a <= in the filtering then:
Count of Closed Journals V2 =
CALCULATE (
DISTINCTCOUNT ( Sheet1[Journal number] ),
FILTER (
ALL ( Sheet1[Closed Date] ),
Sheet1[Closed Date] = MAX ( 'Date'[Date] )
&& NOT ISBLANK ( Sheet1[Closed Date] )
)
)
Count of created Journals V2 =
CALCULATE (
DISTINCTCOUNT ( Sheet1[Journal number] ),
FILTER (
ALL ( Sheet1[Created date] ),
Sheet1[Created date] = MAX ( 'Date'[Date] )
)
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
The problem here is that the 'Created Date' and 'Closed Date' columns in table 'Sheet1' include a time element and are set to the 'Date/Time' data type.
It is important to remember that when you create a relationship with a date table, the column on the many side should only include the date (no time element) and be set to the 'Date' data type.
If you cannot remove the time element in the source data, you can do so in Power Query.
You can then create your relationships:
Once you've done this, and your relationships work properly, you can create 2 very simple measures
to give you the desired results:
Journals Opened = COUNTROWS(Sheet1)
Journals Closed =
CALCULATE (
COUNTROWS( Sheet1 ),
USERELATIONSHIP('Date'[Date], Sheet1[ClosedDate] )
)
I hope that helps!
I have came across this and have a solution for you...
You can unpivot the Created date and Closed date columns in Query Editor. Please refer this link below:
https://www.newtechdojo.com/unpivot-columns-in-power-bi/
Try and please do reply for any more clarifications. Cheers !!!!!!
Deepan
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |