Forum Discussion

abdul_kalam84's avatar
abdul_kalam84
Frequent Visitor
5 years ago
Solved

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 vis...
Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    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:

     

    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:

     

    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.

amitchandak's avatar
amitchandak
Super User
5 years ago

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

abdul_kalam84's avatar
abdul_kalam84
Frequent Visitor
5 years ago

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

 

 

 

 

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.