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 a little problem to solve. I have a Dataset which has two date columns one is called create_dt and the other one is close_dt
I want to count the numbers of create and closed based on Year and month
so my X- Asis should have
Year and Month (not from creat_dt or close_dt)
My Y axsis should have a bar chat with two bar one for each count
I was able to achieve it with one count but the second count value is incoorect. can someone help me
Thanks in advance
Solved! Go to Solution.
Hi @Nashy,
Based on my understanding, you should put Year and Month on X-Asix not form creat_dt or close_dt, so if the start date is 2017/1/2 and the last date is 2017/1/10, so the row should be count when date on X-Asix 2017/1/5, right? If it is, please review the solution.
My sample data.
Create a Calendar table named 'DateTable' not related to my sample date above. Create a measure in Calendar table to get each day.
Selected = CALCULATE(MAX(DateTable[Date]),FILTER(DateTable,DateTable[Date]<=DateTable[Date]))
Create a measure to count the numbers of create and closed based on Year and month.
Count11 = CALCULATE(COUNTROWS(Table3),FILTER(Table3,Table3[Month_Start_Date]<=DateTable[Selected]&&Table3[Month_End_Date]>=DateTable[Selected]))
Create a line chart, select the 'DateTable'[Date] as asix, the Count11 measure as value, you will get expected result.
Please let me know if you have any issue.
Best Regards,
Angelia
Hi @Nashy,
Based on my understanding, you should put Year and Month on X-Asix not form creat_dt or close_dt, so if the start date is 2017/1/2 and the last date is 2017/1/10, so the row should be count when date on X-Asix 2017/1/5, right? If it is, please review the solution.
My sample data.
Create a Calendar table named 'DateTable' not related to my sample date above. Create a measure in Calendar table to get each day.
Selected = CALCULATE(MAX(DateTable[Date]),FILTER(DateTable,DateTable[Date]<=DateTable[Date]))
Create a measure to count the numbers of create and closed based on Year and month.
Count11 = CALCULATE(COUNTROWS(Table3),FILTER(Table3,Table3[Month_Start_Date]<=DateTable[Selected]&&Table3[Month_End_Date]>=DateTable[Selected]))
Create a line chart, select the 'DateTable'[Date] as asix, the Count11 measure as value, you will get expected result.
Please let me know if you have any issue.
Best Regards,
Angelia
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |