Last 6 Months Data in Barchart

[damit23183]

Hi,

I am trying to calculate Last 6 months of count. I have two tables, Appointment Table and Date Table both are mapped with Date column. ALso, created new column to get MONTH-YEAR format so i can use that in BARCHART.

I have already created Measure and barchart but whenever i select single YEAR and Month, barchart is not showing last 6 months of data. Its only show selected month data with inclusion of value from all 6 months of data.

For Example from below screen shot, if i select YEAR = 2020 and Month = September then I should see September, August, July, June, May,April months of data in barchart. Each Month has 100,000 value so i should 6 column with each 100,000 value, but i do see 600,000 in SEPTEMBER column alone. Please refer screen shot below;

To calculate Rolling N Measure, this is DAX formula I have used =

Calculate(DistinctCount(ID), DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-6,MONTH))

If there is one month showing then i should only see 100,000 value not 600,000. 

Any Adivce/Suggestion appreciate!

NOTE: I have tried many solution given by @amitchandak and other couple of blogs online but nothing is working.

Thanks

[negi007]

@damit23183 I have one workaround for your problem. 

Step 1: Create a column on date table 

Month_Short = FORMAT('Vol-Data'[Date],"mmm")

Step 2: Create a measure to calculate YTD value. You can use New Quick Measure option also or use below code and change it as per your data fields

SalesYTD =

IF(

    ISFILTERED('Vol-Data'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    TOTALYTD(SUM('Vol-Data'[Vol (Bio)]), 'Vol-Data'[Date].[Date])

)

Step 3: Plot Month_Short on Axis and SalesYTD on values and your output is below

[damit23183]

Hi,

Thanky you for your response Really appreciate it.

I will definately try your version too.

THanks

[amitchandak]

@damit23183 , Most of the time this solution works

https://www.youtube.com/watch?v=duMSovyosXE

But I can checkout it is not working for. 

Rolling put value in one bucket. So need another table to show a trend.

I think I might have given this solution in the past or rolling formula. 

[damit23183]

Hi Amit,

Thanks for your quick response.

Well, I am not using ROLLING QUICK MEASURE. I am just using simple measure and applied DAX formula that I have mentioned.

Yes I have followed the video (Atteched by you) already, but with that way I am not even getting data in bar chart. If i apply that logic and select Year and Month then output of BARCHART is EMPTY.

Any other idea to get this done?

Thanks

[amitchandak · Accepted solution]

@damit23183 , Check the attached file after the signature . Check page 6 month

[damit23183]

Hi Amit,

Awesome your trick work perfectly.

Actually I was not using extra DATE column which is why result was not coming as expected.

Everything working fine.

Thanks a lot