12 Months Trailing graph using custom Calendar

[Landcrab]

Hi there,

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.

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.

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%

Why do they end of different percentage values?

How can I make  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.

I do have a test PBIX file that I can share but not sure how to do that here, no attachments allowed it seems? 😞

Any help is greatly appreciated.

[Landcrab]

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

[CheenuSing]

Hi @Landcrab ,

I checked the pbix file.  By putting [ACIDTestRatio] as column values in the bar graph the values are the same.

Can you explain what you want achieve in [AcidTestRatioDisconected]. Is this measure different from the ACIDTestRatio.

Cheers

CheenuSing

[Landcrab]

Hi @CheenuSing ,

Thank you for having a look at it.

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.

https://1drv.ms/u/s!Ao4G1JQhVsM1ki5CKnWXem3Hzo48

Our question is how to return only the last 12 months from the slicer/ CurrentDate/ PreviousDate in the CalculationResult in [ACID TEST Ratio Disconnected]??

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.

Note - this is a week based calendar, which means inbuilt functions such as TOTALYTD do NOT work for calculations we want to do.

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.

The VARs used in  [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.

If only we can get at the Column name of the variable we could filter it between Current and Previous dates, otherwise blank.

Any help is greatly appreciated.

[CheenuSing]

Hi @Landcrab 

Can you share the script for generating your calendar table ?

Cheers

CheenuSing

[WayneFindley]

I'm LandCrab's work colleague, and coded the queries involved here.

The Calendar is queried directly from the ERP, which has a Calendar table with FY, Period, start and end dates.  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.  Summary expressions in DAX use a standard form of expression based on choices from the wide selection of start-end dates.  [YTD Actual] expression is an example.

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.

So there is no DAX 'calendar generation script':  the query is packaged as a View in the originating ERP company, and results in a Table within the model.