Need to get line graph on top of the bar graph

[lakshmi_G]

Hi,

I have a requirement that in line and stacked column chart i added the date field as x-axis and in bar chart added trailling 12 month sales and in line graph added trailing 12 month previous year sales.
Here the issue is i am getting different time frame for line and different time frame for bar chart as both are showing current year and previous year.

when i select the date from the slicer both are showing last 12 months data dynamically.

i need to show the previous year line on top of the bar.

as it is different time frames for current year and previous year i am getting this as seperate line and bar.

But i need to represent like  above graph which shows current year vallues in line and previous year in bar

and it should change based on the month-year selected from the slicer.

here important thing is current year sales is calculated to represent like trailing 12 months and previous year sales is calculated for trailing 12 months with different time frame.

Here is the dax for current year 

TTM_Throughputs =

VAR CurrentDate = MAX(Datetable[EndOfMonthDate])

VAR PreviousDate =CurrentDate-365

VAR Result =

CALCULATE(DISTINCTCOUNT(Cokpit_Page1_TP[ThroughputsID_PM]),

FILTER(Cokpit_Page1_TP,

Cokpit_Page1_TP[EndOfMonthDate] >= PreviousDate && Cokpit_Page1_TP[EndOfMonthDate] <= CurrentDate

)

)

Return Result

and for previous year

TTM_ThroughputsPY =

VAR CurrentDate = MAX(Datetable[EndOfMonthDate])

VAR PYCurrentDate = CurrentDate-365

VAR PreviousDate =PYCurrentDate-365

VAR Result =

CALCULATE(DISTINCTCOUNT(Cokpit_Page1_TP[ThroughputsID_PM]),

FILTER(Cokpit_Page1_TP,

Cokpit_Page1_TP[EndOfMonthDate] >= PreviousDate && Cokpit_Page1_TP[EndOfMonthDate] <= PYCurrentDate

)

)

Return Result

As both quries are working fine ang got the correct values but getting different time frame when i added it in a visual

i want to display it like single time frame for last 12 months fro the slelected date and show the values previous year for the selected 12 months period and curre

[v-yanjiang-msft · Accepted solution]

Hi @lakshmi_G ,

According to your description, I create a sample.

Here's my solution. Create a measure.

TTM_ThroughputsPY =
IF (
    [TTM_Throughputs] = BLANK (),
    BLANK (),
    CALCULATE (
        SUM ( 'Cokpit_Page1_TP'[ThroughputsID_PM] ),
        SAMEPERIODLASTYEAR ( 'Cokpit_Page1_TP'[EndOfMonthDate] )
    )
)

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@lakshmi_G , Make sure you use date table joined with you table. In slicer and vsiaul use column from date table

a measure like example can help

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

12 month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-12,Month))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.