Six Months Before and After Calculation

[MikeMCFRS]

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:

[v-yuta-msft · Accepted solution]

@MikeMCFRS ,

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.

[MikeMCFRS]

@Vyk , 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.

[lc_finance]

Hi @MikeMCFRS ,

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