Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
AdamD123
Helper I
Helper I

Monthly Usage Calculation

Hello,

 

I have a calculation which is working for some lines but not otherS, I was hoping someone could point me the right direction as to where I have gone wrong?

 

I am trying to work out an average monthly usage per line, since January 2022. For example PP0002, we have used 511x (Qty of postings) since the beginning of 2022. This equates to an average of 18.25 (511 / 28 (completed months) = 18.25).

 

Whereas PP0001, we have used 4x (Qty of postings) since the beginning of 2022. This equates to an average of 0.14 (4 / 28 = 0.142587) but this is showing as 1.33?

Monthly Usage 1.PNGMonthly Usage 2.PNG

Any help is greatly appreciated.

 

Kind regards,

Adam

 

1 ACCEPTED SOLUTION
v-zhengdxu-msft
Community Support
Community Support

Hi @AdamD123

 

I speculate the issue can be caused by the code:

 

DATEDIFF(MIN (ItemLedgerEntries[Posting_Date]),MAX (ItemLedgerEntries[Posting_Date]),month)

 

 

Maybe you can try to output the result for just this piece of code and see if it's 28.

If not, please try this:

 

MEASURE =
VAR _CountMonth =
    CALCULATE (
        DATEDIFF (
            MIN ( ItemLedgerEntries[Posting_Date] ),
            MAX ( ItemLedgerEntries[Posting_Date] ),
            MONTH
        ),
        ALLSELECTED ( ItemLedgerEntries )
    )
RETURN
    SUM ( ItemLedgerEntries[Quantity] ) / _CountMonth

 

 

Hope it helps,

 

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-zhengdxu-msft
Community Support
Community Support

Hi @AdamD123

 

I speculate the issue can be caused by the code:

 

DATEDIFF(MIN (ItemLedgerEntries[Posting_Date]),MAX (ItemLedgerEntries[Posting_Date]),month)

 

 

Maybe you can try to output the result for just this piece of code and see if it's 28.

If not, please try this:

 

MEASURE =
VAR _CountMonth =
    CALCULATE (
        DATEDIFF (
            MIN ( ItemLedgerEntries[Posting_Date] ),
            MAX ( ItemLedgerEntries[Posting_Date] ),
            MONTH
        ),
        ALLSELECTED ( ItemLedgerEntries )
    )
RETURN
    SUM ( ItemLedgerEntries[Quantity] ) / _CountMonth

 

 

Hope it helps,

 

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-zhengdxu-msft ,

Thank you for your reply and help, your code seems to have worked! I can't say I understand it (my lack of Power BI knowledge) but it is certainly showing the correct figures, which I would expect to see when manually calculating.

Thank you ever so much for the help!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.