How to exclude starting month from a previous month calculation

[Waseem]

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

[v-yuezhe-msft · Accepted 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

[v-yuezhe-msft · Accepted 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]

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 

[v-yuezhe-msft]

@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

[Waseem]

Hi 

This is my snapshot. Appreciate it

[v-yuezhe-msft · Accepted solution]

@Waseem,

Please change your DAX to the following:

Last Date = IF(Subs[Month Year]=EOMONTH(Subs[Month Year],0),1,0)

Regards,
Lydia