filter Date to count for current date and last date of the same column on an inactive relationship

[Sabarikumar7579]

Hello,

I am stuck in create a dax calculation which counts number of entries based on single column. The facttable has 2 Date column - Date of Joined (DOJ) and Tentitive Date of Joining (TDOJ).  DOJ and TDOJ will have different dates.

DOJ is connected to calendar table to the date column and is active. There are slicers built using Calendar table columns like "Month" slicer. TDOJ has inactive relationship with Date column of the calendar table.

My ask is find count of TDOJ for a selected month  to future available months.  I am able to find the value for selected month by using userelationship function but unable to sum up the future months count.

Example:

Name	DOJ	TDOJ
John	Jul-17	Aug-17
Kim	Aug-17	Sep-17
Tom	Sep-17	Oct-17
Jack	Oct-17	Nov-17

As mentioned about DOJ is connected to Calendar table and slicer of Month from calendar table is used.

When we select Sep in the slicer the TDOJ count should be 3 and similar if Aug is selected the count should be 4

appreciate your help in figuring this out.

[MFelix]

Hi @,

Make a summarize table with the following code:

TDOJ_COUNT = SUMMARIZE(ALL(DOJ_Count[TDOJ]),DOJ_Count[TDOJ],"COUNT",1)

Then create the following measure:

Count_TDOJ =
VAR DATE_SELECTED =
    DATE ( YEAR ( MAX ( 'Calendar'[Date] ) ), MONTH ( MAX ( 'Calendar'[Date] ) ), 1 )
RETURN
    CALCULATE ( SUM ( 'Table'[COUNT] ), 'Table'[TDOJ] >= DATE_SELECTED )

I made the Date_Selected starting in the first day of the month since I from what i understood you want to count the current month plus the next months also assuming that you also choose the year in the slicers and not only the month.

The resul is something like this:

Regards,

MFelix

[Sabarikumar7579]

Hi MFelix,

Thank you for your response. I tried your suggestion but for some reason its not giving the right output. link to download the PBX file. Please let me know where i am doing wrong.

Page 2 contains the actual number to showup.

Link to download PBIX file

[MFelix · Accepted solution]

Hi @Sabarikumar7579,

My error since your sample data only add one value per month I used the variable 1 for each date your formula on the summarize table must be change to:

TDOJ_Table =
SUMMARIZE (
    ALL ( Joiner[TDOJ] ),
    Joiner[TDOJ],
    "Count", COUNT ( Joiner[TDOJ] )
)

Like this you are retuning the number of date repetions in your table so giving you the total counts that you need.

See the detail below and the PBIX file.

I also made the order of the months so that it would be in alphabetic order 🙂

Any questions please tell me

regards,

MFelix

[Sabarikumar7579]

Thank you so much!

This work exactly perfect.

Regards
Sabari K

[MFelix]

Hi @Sabarikumar7579,

download link not working.

MFelix

[Sabarikumar7579]

Sorry about it.

Here is the link: 

https://1drv.ms/u/s!AMht7QhPGrBBhzo

Regards

Sabari K