Have a question on how to get the late year totals.
Fact table has the Journal entries and fiscal year & periods . Fiscal year is from May to April.
I see that most examples use the date table and set relation with date in the fact table. How can the time series function if i do not have a date in the fact table ?
Need to provide a totals for location wise comparison. Reading through the time series functions and was not sure if i would need a date table to resolve this, but since fact does not have a date how can that help.
Here is my solution for your post. All I did was go into the query editor and created a new column based on examples from your Fiscal Year & Fiscal Period columns. I also added a Date table and made May as the start month of your Fiscal Year. Your model should be setup more completely now. Let me know if you need any further help.
1 - on the fiscal Year Slicer - look at the formatting tab (paint roller) then select the Selection Controls drop down and enable the Show “Select all” Option.
2 - copy your table
3 - convert the copied table and convert to a matrix layout
3 - on the fields tab of the visualizations your rows should have the fields Fiscal year and Account
4 - Columns Location field
5 - Values Amount
6 - expand the Fiscal Year in the new matrix table
for the pie chart
7 - add account to the Legend section.
Thanks @JarrettM .
But my point was the fiscal year ends on 04/30. I have added a journal date to the model. The reason journal date is not reliable is journals could be dated in May for the postings to the last period of the FY. You can see this model with Journal # 11700.
Also, when i try to see the FY19 with the slicer , visual was blank. i will have a look. but wanted to share and thanks for the model.
I just messed up on the calculation of my Date field, because I went off Fiscal year of Jan-Dec. Your Journal Date is what mine should have been. There has to be somewhere where you can pull data that ties into what month to apply the journal entry to? Here is revised model after I made change in relationship with the Date Table. Now FY 19 is not blank.
Thanks @JarrettM.
When i try to calculate the last year total it still messes up.
Last Year Total = CALCULATE([Total Amount],PREVIOUSYEAR(‘Date’[Date],“04/30”))
Your calculation is a bit off. Here is new solution with two different examples to get the same#. You can choose which one you want. One uses SAMEPERIODLASTYEAR, and other uses DATEADD.
Here are the measures:
Last Year Total DATEADD =
CALCULATE ( [Total Amount], DATEADD ( 'Date'[Date], -12, MONTH ) )
Last Year Total SAMEPERIODLASTYEAR =
CALCULATE ( [Total Amount], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Hi @train, we’ve noticed that no response has been received from you since the 26th of June. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
