Last 12 Months from Selected Date all months also those without data?

[Anonymous]

Hi All,

I wonder if someone could help me with showing all 12 months, even those without data.

I was able to create last 12 months graph using that technique and its working fine, however when I'm selecting region with no items for particular month its not showing on bar chart.

DAX for Power BI - Last 12 Months from Selected Date (Trailing 12 Months TTM) - YouTube

12MonthF=

VAR CurrentDate = MAX('Report Date Table'[End of Month])

VAR PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))

VAR Results = 

CALCULATE(

    DISTINCTCOUNT(Reporting_11282023801AM[F ID]),

    FILTER(

        Issues_Reporting_11282023801AM,

        Reporting_11282023801AM[Created on Date] >= PreviousDate && Reporting_11282023801AM[Created on Date] <= CurrentDate

    )

Return

Results

Example with region with data for all months:

 Example with region where we dont have data for all months:

I tried following solution:

- right click on x-axis date show items with no data

- modified formula to replace blank with 0

Solution work partially, I see months without data but Im losing that 12 month restriction. 

I would like to avoid adding any other slicer, I want to keep it very simple, you are selecting end date and that’s it. Like in video example.

Thanks in advance. 

[lbendlin]

VAR PreviousDate = EDATE(CurrentDate,-12)

[Anonymous]

Thank you, but I dont see any diffrence in results bettwen:

VAR PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))

and

VAR PreviousDate = EDATE(CurrentDate,-12)

 
I still see last 12 months, and showing items with no data  have same results I see everything since beggining till end

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...