Forum Discussion
Last 12 Months from Selected Date all months also those without data?
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.
3 Replies
- lbendlin
Super User
VAR PreviousDate = EDATE(CurrentDate,-12)- AnonymousNot applicable
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
Super User
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-Forum/ba-p/963216
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/1447523