Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Six Months Before and After Calculation

Hello All,

I am trying to calculate six months cost before and after a month. I reserached the forum and found some good formula below:

6MonthBefore+Calclaute(Sum(****,Datesinperiod(******). I am attaching a screenshot below. The first Six months calculation worked but the 6months after leaves the column empty. Any ideas from the forum will be appreciated:

 

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    Anonymous ,

     

    LASTDATE() return the last date in your column. If you plus 6 month based on the last date, still return the last date, so the interval date in DATESINPERIOD() function is nothing, so the result is blank. In this senario, you should use FIRSTDATE() instead.

    6MonthsBefore =
CALCULATE (
    SUM ( Bill[Gross Charges] ) - SUM ( Bill[Gross Collections] ),
    DATESINPERIOD (
        'Bill'[DOS].[Date],
        FIRSTDATE ( 'Bill'[DOS].[Date] ),
        +6,
        MONTH
    )
)

    Community Support Team _ Jimmy Tao

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

     

3 Replies

  • lc_finance's avatar
    lc_finance
    Icon for Solution Sage rankSolution Sage
    6 years ago

    Hi Anonymous ,

     

     

    can you share a sample Power BI file with the formula that is not working?

    That will make it easier to help you. You can share your sample PBI file using One Drive, Google Drive or another similar tool.

     

    Regards

     

    LC

    Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Anonymous ,

     

    LASTDATE() return the last date in your column. If you plus 6 month based on the last date, still return the last date, so the interval date in DATESINPERIOD() function is nothing, so the result is blank. In this senario, you should use FIRSTDATE() instead.

    6MonthsBefore =
CALCULATE (
    SUM ( Bill[Gross Charges] ) - SUM ( Bill[Gross Collections] ),
    DATESINPERIOD (
        'Bill'[DOS].[Date],
        FIRSTDATE ( 'Bill'[DOS].[Date] ),
        +6,
        MONTH
    )
)

    Community Support Team _ Jimmy Tao

    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
      6 years ago

      Anonymous , Hi There, your solution worked great for me. Although I must mention that the "The Before and After cost" did not add up to the total cost in the middle. I will investigate further why this is the case. If you have any ideas feel free to share.

       

      Once again. Thanks for your help.