Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
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.
User | Count |
---|---|
57 | |
21 | |
21 | |
19 | |
16 |
User | Count |
---|---|
86 | |
84 | |
52 | |
37 | |
23 |