12 Months Trailing graph using custom Calendar

Question

Hi there,&nbsp;I am working with a custom week based financial calendar for these reports thats taken straight out of the ERP system. Which means all DAX functions like SAMEPERIODLASTYEAR cannot be used to construct measures.&nbsp;My objective is to create an ACID test ratio and Year-to-Date graph visuals that shows the selected Period End Date and 12 months/ periods back from that.&nbsp;In a Table and Matrix visual the ACID test ratio values show correctly, its only when I create a disconnected calendar (Period End Dates) to show the 12 months historical that the values change, example, select slicer PeriodEndDate = 31/01/2018, click 'Apr 2018' from the Bar chart and compare that to the Table value for the same period. Table says the ACID test ratio for Apr 2018 = 35.63 and the Bar chart for Apr 2018 = 6.46%&nbsp;Why do they end of different percentage values?How can I make&nbsp; the value in the Table is the same in the Bar chart whilst showing 12 months trailing?The second, and most likely simular problem/ solution required is when I create a Bar chart for Year-to-Date, where I am expecting it to show the same values as in the Table visual and the monthly figures should accumulate for the 12 months trailing based selected slicer date.&nbsp;I do have a test PBIX file that I can share but not sure how to do that here, no attachments allowed it seems? :(&nbsp;Any help is greatly appreciated.&nbsp;&nbsp;&nbsp;

anonymous · Answer

I'm LandCrab's work colleague, and coded the queries involved here.&nbsp;The Calendar is queried directly from the ERP, which has a Calendar table with FY, Period, start and end dates.&nbsp; The query cross-applies a Tally table to fill in all dates between Start and End of each period via DateAdd's, and then generates start-end date pairs for all combinations needed, for every date.&nbsp; Summary expressions in DAX use a standard form of expression based on choices from the wide selection of start-end dates.&nbsp; [YTD Actual] expression is an example.&nbsp;All this is necessary because the calendar is essentially weekly, although not for all financial years, and inbuilt PowerBI expressions like TOTALYTD simply don't work across all FY's.&nbsp;So there is no DAX 'calendar generation script':&nbsp; the query is packaged as a View in the originating ERP company, and results in a Table within the model.

landcrab · Answer

Here is the PBIX file shared from OneDrive &gt; https://1drv.ms/u/s!Ao4G1JQhVsM1ki3KNfhdNnpE49cW

anonymous · Answer

Hi Landcrab&nbsp;,
&nbsp;
I checked the pbix file.&nbsp; By putting [ACIDTestRatio] as column values in the bar graph the values are the same.
&nbsp;
Can you explain what you want achieve in [AcidTestRatioDisconected]. Is this measure different from the ACIDTestRatio.
&nbsp;
Cheers
&nbsp;
CheenuSing

landcrab · Answer

Hi Anonymous ,&nbsp;Thank you for having a look at it.&nbsp;The PBIX file has been updated to try and simplify the question and here is hopefully a better explanation of what we are trying to acheive.&nbsp;https://1drv.ms/u/s!Ao4G1JQhVsM1ki5CKnWXem3Hzo48&nbsp;Our question is how to return only the last 12 months from the slicer/ CurrentDate/ PreviousDate in the CalculationResult in [ACID TEST Ratio Disconnected]??&nbsp;At present, if a single Financial Year (FY) is selected this occurs, BUT, as soon as we choose a date where the PreviousDate is in a previous FY the calculation result returns data from the Start of previous FY to the end of the FY coorsponsding to the selected date which is wrong.&nbsp;Note - this is a week based calendar, which means inbuilt functions such as TOTALYTD do NOT work for calculations we want to do.&nbsp;See [YTD Actual] and [Total Movement Actual] measures for our implementation of YTD.The 'Gl Balances'[PeriodMovement] are summable only within the same financial year. This is because 'Period 1' contains an Opening Balance.&nbsp;The VARs used in&nbsp; [ACID Test Ratio Disconnected] have been repeated in a seperate measure called [ALL VARs]and is shown in concatenated card visual on the page, so we know what values we are working with.&nbsp;If only we can get at the Column name of the variable we could filter it between Current and Previous dates, otherwise blank.&nbsp;Any help is greatly appreciated.

anonymous · Answer

Hi Landcrab&nbsp;
&nbsp;
Can you share the script for generating your calendar table ?
&nbsp;
Cheers
&nbsp;
CheenuSing

Forum Discussion

12 Months Trailing graph using custom Calendar

5 Replies

Related content

12 months trailing measure

Trailing Month Slicer

Remove trailing commas

Trailing 12 months DAX

Fabric Free Trail