Calculating nr of days and average nr of sales based on Calendar filter

[CasperSV]

Hi all,

I am trying to calculate the 'Number of days' and 'Average number of sales per month' based on the between dates slicer.

The number of days is not changing to the desired 306 days from march 2021 till december 2021.

Dag=Day, Kalender=Calendar 

The average is not working as well :'/, It should devide 13 with 10 months. But there were no sales in July, August and December, so the the dax devides the total sales with 7.

Maand=month

Do you guys have any idea how to fix this?

[parry2k · Accepted solution]

@CasperSV change your measure to

Number of Days = COUNTROWS ( Kalendar )

and remove  .maand in your average measure.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[v-yalanwu-msft · Accepted solution]

Hi, @CasperSV ;

Try it.

Average sales = DIVIDE([Number of Sales], COUNTROWS( SUMMARIZE('Kalendar',[Date].[Year],[Date].[Month])))

I tested it, and it should work. Looking forward to your reply

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CasperSV]

It worked! 

Thanks a lot guys.

[CasperSV]

Hi guys,

no one any idea how to fix the average number of sales per month?

When I remove .maand (.month), the average is 1:

When adding .maand (.month), the average is 2 (which is correct):

However, as soon as there are no sales in July and August, the average stays 2 but it should change to 1,2.

Hope you guys know the solution.

[parry2k · Accepted solution]

@CasperSV change your measure to

Number of Days = COUNTROWS ( Kalendar )

and remove  .maand in your average measure.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[CasperSV]

Hi Parry2k,

Thanks! The Number of days is now working 😊

However, removing .maand did not work :'/. 

It should be 1,083333 and dynamic based on the between calendar slicer.

[Thejeswar]

Hi @CasperSV ,

When you are doing AVERAGEX, if your data is at the level of date, the AverageX is going to be done by number of days.

Instead you can try using DIVIDE function to get the sales by month. Something like the one below

Mymeasure = DIVIDE(SUM(table1[Sales]), DISTINCTCOUNT(table[Date].[Month]))

May be if you can share more info on how your data looks, ppl. in the community might be able to help you