Hi all,
I have a line chart which needs to display the selected year and the previous year.
In the example in the attached picture with 2020 as the selected date, I would like the chart to show 2019 and 2020.
Can I achieve this via DAX? I really appreciate any help or guidance on how to solve this.
The measure for the selected date slicer I have is:
Selected Date = IF( ISBLANK( [Due Date] ), BLANK(), MIN(‘Dates’[Date] ))
Best Regards
Arve Haugland
Hello @arve.haugland,
Thank You for posting your query onto the Forum.
Well, since we don’t have any idea about how the measures are being used/referenced/named in your PBIX file. But the answer here is - Yes, you can definitely achieve the results by using DAX measures. Below is the screenshot provided of the result -
I’m attaching the working of my PBIX file for your reference purposes.
Hoping you find this useful and meets your requirements that you’ve been looking for. 
Thanks and Warm Regards,
Harsh
Current Year v/s Previous Year - Harsh.pbix (595.6 KB)
Hi @Harsh ,
Thank you for your reply and suggestion!
I appreciate that it is difficult to suggest an approach without looking at the PBIX file.
I have anonymised the content of the PBIX file and attached it for reference now.
Currently I am using a total value per grouping category as the value input to the chart.
Note that since I am using grouping I do not have any active relationships to the calendar.
However, I believe that you showed me a way out of this.
What I believe I have to do is to create a new measure for the grouping category, which is filtered on the selected year and the previous year.
Do you believe that this sound like good approach?
The measure used as value input to the chart is this;
Receivables Per Group Overdue =
VAR _Overdue = CALCULATE( [Invoice Values Days Left],
FILTER(
Invoices,
COUNTROWS( FILTER( ‘Aging Groups’, [Days Left] < 0 ) ) ) )RETURN
IF( _Overdue = BLANK(), 0, _Overdue )
Note that I have tried the solution provided in the topic below, but I was not successful in implementing it. I never fully understood why I didn’t get this to work, but I suspect that it might be due to lack of an active link to the calendar…?
Show SelectedYear and Future Years data in Bar Chart - DAX Calculations - Enterprise DNA Forum
Kind Regards
Arve
Accounts Receivable Insights_EDNA_DEMO.pbix (1.6 MB)
I did some analysis, comparing the Aging formulas “Receivables Per Group Overdue” as stated above with the Aging formula, as discussed at Invoice payment and running balance YoY - #12 by deltaselect .
Please find analysis results in attached PBIX, pages “AR Current & LY” and "Analysis 31 10 21 ", the comparing Aging formulas can be found at measure group “1 DS AccReceiv”, added is a “Aging Groups v2”- table, needed for the comparing Aging formula.
Accounts Receivable Insights_EDNA_DEMO v4.pbix (1.6 MB)
Findings: (be aware to filter on AR Grouping “Overdue” for the comparing Aging formula)
IF the Total Aging of okt 2021 must be 110.176.794, which appears when selecting the Date 31.10.2021 in a slicer and using the formula “Receivables Per Group Overdue” :
then I would recommend using the previously discussed Aging formula.
Reason behind is that this formula gives the right Aging when selecting monthly periods, and this formula provides Last Year Aging with minor adjustments of the formula (see also previous forum solution).
In that case, be aware to use the different Grouping table, needed for that Aging formula, and to filter on AR Grouping “Overdue”
Kind regards, Jan van der Wind
Hi @deltaselect, Thank you for always providing this group with great solutions. We greatly appreciate your efforts and your share of knowledge.
Hi @arve.haugland , 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.
Hi @deltaselect ,
I truly appreciate the deep dive you have done in analysing these figures!
Some amazing and good work done there 
I will use your code as presented in the latest file and in a separate topic as well, and I need to truly understand why the figures are correct when looking at them for a single month, but not when listing them.
The end goal is to have the use select any single date, and then being able to see both the status at that date as well as, in a chart see the status year-to-date and the full previous year.
Ideally the x-axis should show the month name only, not month in year. This way, if the date chosen is in 11 June year xx, the current year line should end in AP6 whilst the whole of previous year should show. Do you have a suggestion on how to show this?
This is sort of showcased in tab ‘AR Overview’ where the legend also show the actual years, but I’m not sure if it easy to make that dynamic.
Kind Regards
Arve
Hi @arve.haugland (v2)
“Ideally the x-axis should show the month name only, not month in year. This way, if the date chosen is in 11 June year xx, the current year line should end in AP6 whilst the whole of previous year should show. Do you have a suggestion on how to show this?”
This can be done, with the possibility of daily receivable amounts like 5 February 2022, as pictured below, using the filter of the AR Group “Overdue”, and a date slicer with slicer setting “Before” instead of “Between”
Furthermore to automatically limit the Receivables to the last year and current year, the “Current Receivables Per Group”-measure is extended with 6 lines, which make the result for other years blank (see the measure below)
PBIX attached:
Accounts Receivable Insights_EDNA_DEMO v7 Months graph.pbix (1.6 MB)
Current Receivables Per Group = VAR FilteredARLedger = FILTER ( 'Invoices', and( 'Invoices'[Document Date] <= max(Dates[Date]), --filter upon all invoices before reporting date or(ISBLANK( 'Invoices'[Clearing date]), 'Invoices'[Clearing date]>max(Dates[Date])))) -- without clearing date or clearing date > reporting date Var AgingGroup = CALCULATE( SUM( 'Invoices'[Invoice Value] ), FILTER( FilteredARLedger, --to speed up the calculation, as the data is filtered COUNTROWS( FILTER( 'Aging Groupsv2', [J-Current Days Due] >= 'Aging Groupsv2'[Min2] && [J-Current Days Due] <= 'Aging Groupsv2'[Max2] ) ) > 0 ) ) Var EarlierstRepDate = DATE(year(MAX(Invoices[Net due date]))-1,1,1) --last year, based upon invoice due date Var LatestRepDate = DATE(year(MAX(Invoices[Net due date])),12,31) -- current year end, based upon invoice due date return if( and (min(Dates[Date]) >= EarlierstRepDate, max(Dates[Date]) <= LatestRepDate), Aginggroup, BLANK() )-- to limit Receivables to previous year and yearend current year
The report on page “AR Current & LY (months)” :
Kind regards, Jan van der Wind
Hi @deltaselect ,
Really appreciate the time and effort you put down in assisting me with this!
When I selected a date before 2021 the line chart went blank.
However, I did a small change to the code in the measure “Current Receivables Per Group” where the EarliestRepDate and LatestRepDate refer to the selected date measure.
Selected Date = IF( ISBLANK( [Due Date] ), BLANK(), MAX('Dates'[Date] ))
I also added a TOP N filter in the chart, limiting the chart to only show the current (selected) and the previous (selected - 1) year.
It seems to work…and the chart seems to be dynamically reflecting the current and current -1 year.
Do you see any issues with the modifications I did with the “Current Receivables Per Group” measure?
Current Receivables Per Group =
VAR FilteredARLedger = FILTER ( 'Invoices',
and( 'Invoices'[Document Date] <= max(Dates[Date]), --filter upon all invoices before reporting date
or(ISBLANK( 'Invoices'[Clearing date]), 'Invoices'[Clearing date]>max(Dates[Date])))) -- without clearing date or clearing date > reporting date
Var AgingGroup = CALCULATE( SUM( 'Invoices'[Invoice Value] ),
FILTER( FilteredARLedger, --to speed up the calculation, as the data is filtered
COUNTROWS(
FILTER( 'Aging Groupsv2',
[J-Current Days Due] >= 'Aging Groupsv2'[Min2] &&
[J-Current Days Due] <= 'Aging Groupsv2'[Max2] ) ) > 0 ) )
Var EarlierstRepDate = DATE(year([Selected Date])-1,1,1) --last year, based upon the selected date
Var LatestRepDate = DATE(year([Selected Date]),12,31) -- current year end, based the selected date
//Var EarlierstRepDate = DATE(year(MAX(Invoices[Net due date]))-1,1,1) --last year, based upon invoice due date
//Var LatestRepDate = DATE(year(MAX(Invoices[Net due date])),12,31) -- current year end, based upon invoice due date
return
if( and (min(Dates[Date]) >= EarlierstRepDate, max(Dates[Date]) <= LatestRepDate),
Aginggroup,
BLANK() )-- to limit Receivables to previous year and yearend current year
King Regards
Arve
Hi @arve.haugland , v2
Nice feature to select the current and last year: your filtering of TopN 2 [Selected Year] and works fine !!
PS 1 A less preferable, alternative solution is a change of the Dates-slicer setting to “Between” (Start and End Date free to select) and let the user freely select the periods, no need for a blank()-condition in the measure. (No absolute requirement to let the Dates start from the first date in the Dates-table)
Maybe also a DAX-calculated Dates-table with predefined dynamic dates is thinkable.
PS 2: It seems more consistent to use "Max(Dates[Date] instead of the measure “([Selected Date]” at:
Kind regards,
Jan van der Wind
Hi @deltaselect ,
Thank you for your time and assistance!
I have marked your latest post as the solution. This in combination with all you have provided earlier solves the topic in my opinion.
Now I have to incorporate everything into the final model, and distribute it.
Kind Regards
Arve
I am also preparing the aging report based on SAP data.
Which reports need to be extracted from SAP?
Hi @Abhijeet357 ,
I trust this all comes down to what kind of information you are looking for.
I can share the transactions codes I used to pull the required data in the final report, however, I cannot say if these are the reports you need to solve your challenge.
T-Codes used:
VA05
FBL5N
CN55N
ZV88
CJI3
CN43N
Y-MS4_15000003
V/LD
Regards
Arve Haugland
