P & L Statement in Power BI

[edge9999]

I am trying to create a simple P & L Statement with Current month Actuals and prior month actuals.  I have a detailed set of transactional data that has rows similiar to this

Transaction Date

Fiscal Period  (eg 202202)

Account

Amount

Location 

Company

The data table is for multple years.   I have a Fiscal Period table that is not joined to the transactional table that looks something like this.

Fiscal Period

Month

Year

Prior Fiscal Period 1

Prior Fiscal period 2

Prior Fiscal Period 3 etc

Future Fiscal period 1

My plan was to have the user select a Month and fiscal year which would retrieve one record in the Fiscal period table.   From this record, i could then figure out what the current period is as well as the prior or future periods.    The formula i have used for my current month actuals is as follows

MS_CURRMTDAMT = CALCULATE(

sum(VW_NS_TRANSACTION[AMOUNT_DRCR]),

TREATAS(Values(VW_CALENDAR_FISCALPERIOD[POSTINGPERIOD]),VW_NS_TRANSACTION[POSTING_PERIOD])

This formula seems to work and when i change my slicer the period values update.    I tried the same logic for my prior period with the formula being 

MS_PRIOR_1_MTD = CALCULATE(

sum(VW_NS_TRANSACTION_[AMOUNT_DRCR]),

TREATAS(Values(VW_CALENDAR_FISCALPERIOD[POSTINGPERIOD_PRIOR_1] ),VW_NS_TRANSACTION[POSTING_PERIOD])

)

This formula will not return any values.    What am i missing here?   It feels like i'm applying the same logic in both formulas, just using a differnet field from the fiscal period table.   Any thoughts here?

Thanks

[edge9999 · Accepted solution]

As it turns out, i had a data issue.  When the Fiscal period data got loaded, there was a space in front of the prior period which was why it was returning blank.   

[v-kkf-msft]

Hi @edge9999 ,

Because of the TREATAS function, for measure [MS_CURRMTDAMT], the filter ("202202") applied to VW_CALENDAR_FISCALPERIOD[POSTINGPERIOD] will be applied to VW_NS_TRANSACTION[POSTING_PERIOD].

Similarly, for measure [MS_PRIOR_1_MTD], the filter ("202201") applied to VW_CALENDAR_FISCALPERIOD[POSTINGPERIOD_PRIOR_1] will be applied to VW_NS_TRANSACTION[POSTING_PERIOD].

Like this:

But your VW_NS_TRANSACTION[POSTING_PERIOD] does not contain the row with the value 202201, so measure [MS_PRIOR_1_MTD] returns blank.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.