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.
I have a list of records that I receive monthly. The list includes all the current records from the previous month and each month records are added or deleted. Because of this, I am having trouble filtering data so that I only count a record one time and not the SAME RECORD FOR EACH MONTH. In pic 1 you will notice that the same ID number appears for each given month because I am choosing to highlight one record entry.
With that as a baseline I am trying to highlight the number of Records that are new for a given fiscal year. To be clear, for a FY that begins in July, I am trying to get the total number of new records in who's original entry is July 2019 + August 2019 + Sept 2019 + Oct 2019 without repeating the same record month in and month out. For example, the child in Pic 1 should be counted ONCE for entering in FY19 and not four different times. Also, I want it to filter for each past FY. How do I write my DAX so that a child is only counted once and also have to so it provides me with the total sum YTD.
I have tried this:
Solved! Go to Solution.
HI @bw70316 ,
Can you please share some sample data with the minimum data structure and expected result for test? It is hard to coding formula without any detail data.
In addition, you can also take a look at the following blog about use date function to manually define filter range for calculating:
Time Intelligence "The Hard Way" (TITHW)
Regards,
Xiaoxin Sheng
Below is some sample data. So this is a running list of a record that came in July of 2019. Every month I get a list of about 300 records, most of them are the same, some have been added, others removed. I am trying to create a list of children who have entered care in a particular FY. For example, this individuals entered the list in July and September of 2019, the first month of the fiscal year. So the number of children in this list who entered in 2019 is two (based on there being only two unique SIS numbers). I am trying to capture the min date only for each of these records and get a running count for the FY. I would also like it to be dynamic so that if you click on July you get 1 record, but if you click on September/October you get 2 records (since one of the individuals entered in September). Please let me know if you need any more information or clarification. Thank you for your assistance.
SIS Number | Date of Original Placement | Month |
20085875571 | 9/13/2019 | September |
20085875571 | 9/13/2019 | October |
20083769673 | 7/18/2019 | July |
20083769673 | 7/18/2019 | August |
20083769673 | 7/18/2019 | October |
20083769673 | 7/18/2019 | September |
HI @bw70316 ,
Can you please share some sample data with the minimum data structure and expected result for test? It is hard to coding formula without any detail data.
In addition, you can also take a look at the following blog about use date function to manually define filter range for calculating:
Time Intelligence "The Hard Way" (TITHW)
Regards,
Xiaoxin Sheng
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |