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.

v-robertq-msft's avatar
v-robertq-msft
Community Support
5 years ago

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:

 

  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:

 

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.

abdul_kalam84's avatar
abdul_kalam84
Frequent Visitor
5 years ago

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.