Active Month In Use

[bolarinwa]

Hi, I created a calculated column Mont h in use which calculates the number of Months from the service data for each location e.g column 1 is the first service Month and column 6 is 6 Months after. However, I want each next Month to be Months when the service is active, that is only when there are values. For example in row 1 values in months 14-19 should be in columns 6-12.

Below is my dax code 

Months in Use (Location) = FLOOR(DATEDIFF(RELATED(categories[First Feedback]), RELATED('Time'[Start Of Day]), DAY) / (365/12), 1) + 1

Please help.

[bolarinwa]

This is a sample of expected output as compared to the previous table I posted.

[amitchandak]

@bolarinwa , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer if this can help

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

[bolarinwa]

 

These are the example of input data