Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Everyone ,
I Want to Create A Report to Show My Table Record Counts with rolling Months .I Have a CreatedDate Column in My Table .
How can I Create this Visualization with Rolling Months .
Solved! Go to Solution.
Hi @rakeshsaini
On your DATE table, make sure you have the following calculated columns.
MonthID = INT(format('Dates'[Date],"YYYYMM")) & Month = FORMAT('Dates'[Date],"MMM-YY")
Once you have these two columns on your DATE table, make sure the Month column is sorted by MonthID, and use the Month column from your DATE table on the axis of your visual.
Thanksl @Phil_Seamark . Its Working !! but Its Showing All Months From JAN-15 to DEC-17 , We Need to Map Months With Current Month on X-Axis (FEB-16 to FEB-17).
Now , its Showing ..
Try adding this column to your Date table
Months from Today = IFERROR( DATEDIFF('Dates'[Date],TODAY(),MONTH),-1)
Then you can add it as a Report, Page or Visual level filter to only show values between 0 and 12 (or 1 and 12). This will dynamically roll as we go into new months
The new column looks good.
Simply drag the new column into your "Report level filters" box and configure as below (and hit apply)
Could you clarify what do you mean by rolling months ?
Problem :-
The Complaint Count Report > Customer Complaint Count (Rolling 12 months) is NOT actually displaying the past 12 months.
..and the Left-Most Column Should be for FEB-16 , with No Other Records (eg. JAN 2016) displaying in this Visualization.
and IF Today is FEB 27/2017 then the RIGHT MOST Column Should be for FEB-17.
Are you able to share some sample data along with the formula you are currently using for the Customer Complaint Count (Rolling 12 months) measure?
Oh and use a Month column from your Date table on your Axis. The Month Column in a Date table won't have any gaps.
If you use a Month column in your Fact table, you may have gaps.
Thanks to All,
Yes , In COMPLAINTS Table.
My Problem is - This Month is FEB-17 , right !! So We Need to Show FEB-16 ,MAR-16,APR-16.................JAN-17,FEB-17 on X-axis.
From Tommorow MONTH is MARCH So We Need to Show MAR-16,APR-16.................JAN-17,FEB-17,MAR-17 on X-axis.
Hi @rakeshsaini
On your DATE table, make sure you have the following calculated columns.
MonthID = INT(format('Dates'[Date],"YYYYMM")) & Month = FORMAT('Dates'[Date],"MMM-YY")
Once you have these two columns on your DATE table, make sure the Month column is sorted by MonthID, and use the Month column from your DATE table on the axis of your visual.
Thanksl @Phil_Seamark . Its Working !! but Its Showing All Months From JAN-15 to DEC-17 , We Need to Map Months With Current Month on X-Axis (FEB-16 to FEB-17).
Now , its Showing ..
Thanks Phil_SeamMark , Its Working !! But it Showing Months From JAN-15 to DEC-17 . but we need to Only From FEB-16 to FEB-17. Please See Below Screenshot.
Try adding this column to your Date table
Months from Today = IFERROR( DATEDIFF('Dates'[Date],TODAY(),MONTH),-1)
Then you can add it as a Report, Page or Visual level filter to only show values between 0 and 12 (or 1 and 12). This will dynamically roll as we go into new months
Yes i added it and Now it Showing Months Count . But How Can i Map it . Can you Clarify . I am Not able to catch .
The new column looks good.
Simply drag the new column into your "Report level filters" box and configure as below (and hit apply)
Thanks and Yes , I shared Complete Problem!!
Problem :-
The Complaint Count Report -> Customer Complaint Count (Rolling 12 months) is NOT actually displaying the past 12 months.
.The Left-Most Column Should be for FEB-16 , with No Other Records (eg. JAN 2016) displaying in this Visualization.
and IF Today is FEB 27/2017 then the RIGHT MOST Column Should be for FEB-17.
Means I need to Show all Last 12 months From Current Month on X-axis. and Display Counts .
--------------------------------------
What I did !!!
Soluition 1:
SortColumn = YEAR(Complaints[ComplaintDate])&MONTH(Complaints[ComplaintDate])
--
I have done with this way but it Show only that Months those are have counts. Please See below Image .
we Need to Show From Current Month (FEB-17 ) to Last 12 Months ( FEB-16) . Suppose if FEB-16 and APR-16 Do not have Count than it Will display with Count 0 .
--------------------------
Soluition2 :
On the visualisation fields,I have set the axis to 'Show items with no data'
But Problem is Same !!!
I believe you can solve this problem by downloading the "timeline" custom visual from here:
https://app.powerbi.com/visuals/
On this custom visual you can dynamically choose which time period you want to show the data from (ie. the x axis). You might also want to sort your Month Column by an Index column ranging from 1 to 12 so that your months are in the correct order.
Abhi
Hi @rakeshsaini,
It looks like your Month column is sorting alphabetically. This can be fixed by telling the column to sort by other column and to have the other column something that is numeric such as
MonthID = int(format(<somedate>,"YYYYMM")
Hi @rakeshsaini,
Based on your description, it's still confusing, it's different to reproduce your scenario. Could you please share sample data and list the expected result for further analysis?
Best Regards,
Angelia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |