The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
https://community.powerbi.com/t5/Desktop/Count-by-End-of-Month-Reposting/m-p/507546#M237114
Please go through the above link
For example for Student Number 30830 the Effective date for Status Change is on same day but there is other field i didn't mention in sample data is UpdatedDate.
StudentGUID | StudentNumber | StudentStatusID | Status | EffectiveDate | UpdatedDate |
8136E11E-0A6A-4AB9-B7EE-315B72A27280 | 30830 | 2 | Pending | 8/2/2018 | 8/2/2018 |
F102367E-B10F-4D68-B1B9-AFA78550A8EF | 30830 | 3 | Approved | 8/2/2018 | 8/30/2018 |
If the status is changed in same month it should show the updated Status in that month.
For Pending below is the code, but we are getting above result in Pending and Approved.Please let me know if you have any questions and help me out on this.
Pending2 =
VAR __minDate =
MIN ( 'Date'[Date] )
VAR __maxDate =
MAX ( 'Date'[Date] )
VAR __tmpTable =
SUMMARIZE (
tblStudent,
tblStudent[StudentGUID],
tblStudent[StudentNumber],
tblStudent[StudentStatusID],
"__UpdatedDate", MAX ( [UpdatedDate] )
)
VAR __tmpTable1 =
ADDCOLUMNS (
__tmpTable,
"__Status", MAXX (
FILTER (
tblStudent,
tblStudent[StudentGUID],
= EARLIER ( [StudentGUID],)
&& [__UpdatedDate] = EARLIER ( [__UpdatedDate] )
),
[StudentStatusID]
)
)
RETURN
COUNTROWS (
FILTER (
__tmpTable,
[__UpdatedDate] >= __minDate
&& [__UpdatedDate] <= __maxDate
&& tblStudent[StudentStatusID]=2 )
)
Please help me out on this
hi,sunny27
I have tested your formula, there are a few extra commas in it. It works well, StudentNumber 30830 has one Pending
in Aug-2018.
Please share some data sample and the expected output.
Best Regards,
Lin