How to add slicers onto a visual for a dataset that uses a separate date table

[ClemFandango]

Hi all, I am hoping that one of you geniuses can help.  I have two tables:-

Table1 (133 rows of data)

Date	ProductCount
31/01/24	2
30/12/23	1
30/11/23	1
31/10/23	1

Date = end of month

ProductCount = A count of products where ‘Date’ from table 1 is found between (or equal to) the ‘Start’ & ‘End’ from table 2.

DAX from Table1 is as below:-

ProductCount =

COUNTROWS (
    FILTER (
        'Table2',
        [Date] >= 'Table2'[Start Date]
            && [Date] <= 'Table2'[End Date]
    )

)

Table2 (79, 278 rows of data)

Product	Start	End	GroupSlicer	CountrySlice	TypeSlice3
Product 1	01/01/24	01/01/25	test	England	Main
Product 2	25/05/23	25/05/24	Test	Wales	SubMain
Product 3	01/04/20	01/05/21	Test2	USA	Main

I want to create a chart that plots the result from ‘Table1’ (with Date as the x axis) alongside the slicers i.e Group, Country & Type from ‘Table2’. My end goal is to create a chart that calculates the number of products at the end of each month (as per table 1) with a few slicers.

Any idea how I can put table 1 into a visual, with the slicers from table 2? I suspect neither table is setup correctly to do this currently.

Any help will be eternally appreciated!

[Ashish_Mathur]

Hi,

You do not need to create the Product count column in Table1.  Furthermore, a Calendar Table should have one entry per day.  You may refer to my solution of a similar problem in the attached file.

Hope this helps.

[ClemFandango]

Hi @Ashish_Mathur this is amazing!

Do you have any idea how i could get this to provide totals against dates at end of month? (instead of Jan, Feb, Mar etc)?

Thanks agian,

CF

CF

[Ashish_Mathur]

You are welcome.  I cannot understand your requirement.  Apply my measures on your dataset, share the download link of the PBI file and clearly show what you want there.

[amitchandak]

@ClemFandango , I am assuming table1 is date table with all dates , Keep it disconnected then you can have a measure like

Countrows(filter(Table2, Table2[Start Date]<= Max(Table1[Date]) && Table2[End Date]>= Max(Table1[Date]) ))

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

[ClemFandango]

Hi there @amitchandak 

Yes, you are correct. Table is the date table with just dates (all dates are for end of month).

Many thanks for the suggestion. When i try your measure, i get the error message 'The MAX function only accepts a column referance as an argument', which i dont really understand as they are column referances.

Any ideas where i am going wrong?

[ClemFandango]

Hi @amitchandak 

I am unable to share my PBIX, but I will try to illustrate my query better.

My chart is as below

The chart currently calculates totals by each month, however I would like to calculate the totals by end of month.

I have a standard date table setup for each day 01/05/20, 02/05/20, 03/05/20 etc

I have another table as below:-

Table1

Product	Start	End	GroupSlicer	CountrySlicer	TypeSlice
Product 1	01/01/24	01/01/25	test	England	Main
Product 2	25/05/23	05/05/24	test	Wales	Submain
Product 3	01/04/20	01/05/21	test2	USA	Main

This is the measure I am using...

productsatendofmonth = COUNTROWS(FILTER(Table1,[Start Date]<=[Selected max date]&&[End Date]>=[Selected min date]))

Your measure above correctly calculates the number of products each month, but I would like to calculate totals at the end of each month.

Is there any method to count the totals as of the end of each month, instead of just the whole month?

Any help greatly appreciated!

[Anonymous · Accepted solution]

Hi, @ClemFandango 

I created the following sample data:

I use the following DAX to calculate the total at the end of each month:

Count = 
CALCULATE(COUNTROWS(FILTER(ALLSELECTED('Table'),[DATE]>='Table'[Start ]&&[DATE]<='Table'[End])))

I have created the following slicers:

By setting the X-axis type to Categorical, the count at the end of each month is correctly displayed:

Dynamically calculate count through a type selected by the slicer:

I have provided the PBIX file used this time below, I hope it can be helpful to you.

How to Get Your Question Answered Quickly

Best Regards

Jianpeng Li

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

[ClemFandango]

Wow @Anonymous 

Thanks so much for the detailed explanation.

Have you any idea why I dont have the 'Catagorical' Type option?

Oh I just opened the PBIX that you kindly sent, I think that you may have attached the wrong one.

Thanks so much again, It is really appreciated!

[Anonymous]

Hi, @ClemFandango 

I'm very sorry for my mistake. I have re-uploaded my PBIX file now, you can re-download it and view it. Regarding why you don't have the 'Catagorical' Type option, you can check whether your column is of numeric or date type. Please note that if the X-axis is string/boolean, the "Type" option will not show up. It should be numeric or date data type.

[ClemFandango]

Hi @Anonymous @Ashish_Mathur @amitchandak 

I just wanted to say a huge thanks for all of your contributions. You all helped massivley to solve this for me.

Big thanks!