Data Modeling-Measure between Start and End Date

[GhadaMehrez]

Could help me to add new measure calculating the difference between Start and End Date in years and months.

[amitchandak]

@GhadaMehrez , there are few ways.

refer: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

refer attached files after signature can help?

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[GhadaMehrez]

Actually this so confusing to me especially since I'm new to using Power BI but I found this measure and it works well to calculate no. month between Start and End Date:

No.of months= SUMX(Orders, DATEDIFF(Orders[End Date],Orders[Start Date],month))

but the problem here is I want to calculate the number of month between the End Date and Today (Month remains to today) any help please  to add a measure for that 

[v-yiruan-msft]

Hi   @GhadaMehrez ,

Please update the formula of the measure [] as below and check if it can get your expected result...

No.of months = SUMX ( Orders, DATEDIFF ( Orders[End Date], TODAY (), MONTH ) )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards

[GhadaMehrez]

Thanks a lot I created this measure but when I put the X Axis as No of Month measure the projects don't appear in the chart

Could you guide me how to attache PBIX

[v-yiruan-msft]

Hi @GhadaMehrez ,

You can refer the following link to upload the file to the community. Before sharing the file, please remove the sensitive info in your file. Thank you.

How to upload PBI in Community

Best Regards

[GhadaMehrez]

please find attached :

https://drive.google.com/file/d/1u9YQt8RMgDlkQhN1vY9UWbbRumpGKp4Y/view?usp=sharing 

[v-yiruan-msft]

Hi @GhadaMehrez ,

When try to open the shared file, I got the following error message... Could you please create a separate simplified report file: use Enter data method to create the quries and create visual which can be enough to illustrate your problem.

Best Regards

[GhadaMehrez]

Please see here:

https://drive.google.com/file/d/1m3b_PdAxJ89MDKVJ9G23tPhVXAcJ2Lxi/view?usp=sharing

[v-yiruan-msft]

Hi @GhadaMehrez ,

I updated your sample pbix file(see attachment), please check if that is what you want. Please toggle on "Category label" tab.

Best Regards

[GhadaMehrez]

in the original copy it is on but still nothing appear in dashboard 

[GhadaMehrez]

@v-yiruan-msft please as you see here I still cannot see any data in the dashboard but when I clear the x-axis the data appear

any help how to fix it 

and for the measure for the passed due dates it is not calculated as (-) any way how to update the measure 

Thanks a lot