Forum Discussion

AdamD123's avatar
AdamD123
Helper I
2 years ago
Solved

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 a...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

  • AdamD123's avatar
    AdamD123
    Helper I
    2 years ago

    Hello Anonymous ,

    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!