Counts With rolling Months

[rakeshsaini]

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 .

[Phil_Seamark · Accepted 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.  

[rakeshsaini · Accepted solution]

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 ..

[Phil_Seamark · Accepted solution]

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

[Capstone]

Could you clarify what do you mean by rolling months ?

[rakeshsaini]

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.

 

[Phil_Seamark]

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?

[Phil_Seamark]

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.

[rakeshsaini]

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.

[Phil_Seamark · Accepted 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.  

[rakeshsaini · Accepted solution]

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 ..

[rakeshsaini]

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.

[Phil_Seamark · Accepted solution]

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

[rakeshsaini]

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 .

[Phil_Seamark · Accepted solution]

The new column looks good.

Simply drag the new column into your "Report level filters" box and configure as below (and hit apply)

[rakeshsaini]

Thanks You So Much @Phil_Seamark , Its Working Fine.

[Phil_Seamark]

Nice!  Well done @rakeshsaini 🙂

[rakeshsaini]

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 !!!

[Capstone]

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

[Phil_Seamark]

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")

[v-huizhn-msft]

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