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.
Dear Experts
I have a customer table showing product wise numbers at start of month, additions/churn during diferent days of month and the closing number at end of months. I need to prepare graphs to show month wise (on x-axis) number of customers at start and end of months. Since there are different dates in each month, I need to select only first day of month to show opening number of customers and only last day of month for the closing balance. I cannot take average number for each month but numbers on specific dates. I have enclosed the excel file snapshot for quick reference. Appreciate help from Commmunity to resolve it.
Cheers
Solved! Go to Solution.
I need to prepare graphs to show month wise (on x-axis) number of customers at start and end of months. Since there are different dates in each month, I need to select only first day of month to show opening number of customers and only last day of month for the closing balance.
@Waseem,
Please create the following columns in your table, drag the columns to the visual level filters of your graphs, and set their values to 1. If you still have questions, please post expected result based on the above sample data here.
First day of Month = IF(Table[Date]= DATE(YEAR(Table[Date]),MONTH(Table[Date]),1),1,0)
Last day of Month = IF(Table[Date]= ENDOFMONTH(Table[Date]),1,0)
Regards,
Lydia
@Waseem,
Please change your DAX to the following:
Last Date = IF(Subs[Month Year]=EOMONTH(Subs[Month Year],0),1,0)
Regards,
Lydia
I need to prepare graphs to show month wise (on x-axis) number of customers at start and end of months. Since there are different dates in each month, I need to select only first day of month to show opening number of customers and only last day of month for the closing balance.
@Waseem,
Please create the following columns in your table, drag the columns to the visual level filters of your graphs, and set their values to 1. If you still have questions, please post expected result based on the above sample data here.
First day of Month = IF(Table[Date]= DATE(YEAR(Table[Date]),MONTH(Table[Date]),1),1,0)
Last day of Month = IF(Table[Date]= ENDOFMONTH(Table[Date]),1,0)
Regards,
Lydia
Thanks Lydia
I used following for first day of month and it worked:
Start Date = IF(DAY([Month])=1,1,0)
However, the last date is giving me wrong numbers
Regards
@Waseem,
The second DAX works in my scenario. Could you please post a screenshot about your result and DAX ? I also note that there is no Month field in the sample table you post.
Regards,
Lydia
Hi
This is my snapshot. Appreciate it
@Waseem,
Please change your DAX to the following:
Last Date = IF(Subs[Month Year]=EOMONTH(Subs[Month Year],0),1,0)
Regards,
Lydia
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |