cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abdul_kalam84
Frequent Visitor

Month wise total based on week selection

Hi All, I have a Financial year calendar table with date, weekstart, weekend, month and year. My Financial start with 1st of October.

In my report all visual based on weekstart date ,however one visual to display month wise count.

example if i select weekstart as "2020-12-06' need to display count of December, November and October.  

I used below calculation for measure  , but it showing count only for December month.

 

CALCULATE( DISTINCTCOUNT(Tbl_DT[IncidentCounts]),tbl_Calendar[Date]>=YearStart && tbl_Calendar[Date]<=SelectedWeek)

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

Hi, @abdul_kalam84 

According to your situation, if you don’t want to delete the relationship between the two tables, I suggest you to create another Date table which contains [WeekStart] to place into the Slicer which slices the one special visual, you can follow my steps:

  1. Create another Date table and here’s the relationship:

v-robertq-msft_0-1609381071227.png

屏幕截图 2020-12-31 101839.png

 

  1. Change the measure like this:
Count from financial start =

var _selecteddate=SELECTEDVALUE('Date1'[WeekStart])

return

CALCULATE(

    COUNT(Incident[IncidentNo]),

    FILTER('Incident',

[LoadingDate]>=DATE(YEAR(_selecteddate),1,1)&&[LoadingDate]<=EOMONTH(_selecteddate,0)))

 

  1. Create a calculated column in the ‘Incident’ table:
Month = MONTH('Incident'[LoadingDate])
  1. Replace the column in Slicer with ‘Date1’[WeekStart] and change the columns in the Matrix chart like this:

v-robertq-msft_2-1609381071247.png

 

And you can get what you want.

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-robertq-msft
Community Support
Community Support

Hi, @abdul_kalam84 

According to your situation, if you don’t want to delete the relationship between the two tables, I suggest you to create another Date table which contains [WeekStart] to place into the Slicer which slices the one special visual, you can follow my steps:

  1. Create another Date table and here’s the relationship:

v-robertq-msft_0-1609381071227.png

屏幕截图 2020-12-31 101839.png

 

  1. Change the measure like this:
Count from financial start =

var _selecteddate=SELECTEDVALUE('Date1'[WeekStart])

return

CALCULATE(

    COUNT(Incident[IncidentNo]),

    FILTER('Incident',

[LoadingDate]>=DATE(YEAR(_selecteddate),1,1)&&[LoadingDate]<=EOMONTH(_selecteddate,0)))

 

  1. Create a calculated column in the ‘Incident’ table:
Month = MONTH('Incident'[LoadingDate])
  1. Replace the column in Slicer with ‘Date1’[WeekStart] and change the columns in the Matrix chart like this:

v-robertq-msft_2-1609381071247.png

 

And you can get what you want.

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-robertq-msft
Community Support
Community Support

Hi, @abdul_kalam84 

According to your description and sample data, I guess that you want to get the count of IncidentNo from the financial start of the year to the end of the selected month, am I right? You can follow my steps:

  1. Delete the relationship between the two tables, which is important:

v-robertq-msft_0-1609233699919.png

 

  1. Create a measure:
Count from financial start =

var _selecteddate=SELECTEDVALUE('Date'[WeekStart])

return

CALCULATE(

    COUNT(Incident[IncidentNo]),

    FILTER('Incident',

[LoadingDate]>=DATE(YEAR(_selecteddate),1,1)&&[LoadingDate]<=EOMONTH(_selecteddate,0)))

 

  1. Create a Slicer and place ‘Date’[WeekStart], and a Matrix and place like this:

v-robertq-msft_1-1609233699928.png

 

And you can get what you want.

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you @v-robertq-msft ,

 

It works fine...thank you , I have many visuals based on WeekStartDate selection,  so need relationship between incident table and date table . Only in one visual need to display count for selected and previous months  . As per your solution I can achive by using  disconnected table.  Do i need two date tables to achive full report ? If yes then my slicer on which date table ?

 

Once again thank you for your solution.

amitchandak
Super User IV
Super User IV

@abdul_kalam84 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

amitchandak
Super User IV
Super User IV

@abdul_kalam84 , Try a measure like

 

measure =
var _min1 = minx(allselected('Date'), 'Date'[Date])
var _min = eomonth(_max1,-3)+1
var _max1 = maxx(allselected('Date'), 'Date'[Date])
var _max = eomonth(_max1,0)
return
calculate(DISTINCTCOUNT(Tbl_DT[IncidentCounts], filter( Table, Table[Date] >=_min && Table[Date] <=_max))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thank you @amitchandak 

 

I have incident table with Incident number and Loading date (Loading dates are week start dates)

And Date table with Weekstart date , weekend date , Month and Year

 

abdul_kalam84_0-1609184870908.png

 

abdul_kalam84_3-1609185450608.png

 

 

I have a slicer on Week Start date.  if i selected any week of the month,  need to display that week month count and also previous months count 

 

Example If I select "08-11-202" from my slicer, need to display count for all weeks of November  and October  . At present visual showing for Novemeber not for October month.

 

I am a SQL guy , I am looking for measure as below.

 

abdul_kalam84_2-1609185338213.png

 

 

 

littlemojopuppy
Super User I
Super User I

Can you provide some sample data?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors