Forum Discussion
Helper I12 Months Trailing graph using custom Calendar
Helper IHi Anonymous ,
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.
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.