Forum Discussion
Helper IVSingle Date filter for Multiple Columns and add Measures
Hi Kostas ,
Here is the .pbix that you can try it again: PBIX
Put the Date filter measure in the table visual filter and set up value as 1, put the Total measure in a card visual direcrtly and it should work fine.
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Super UserKostas , Refer this article. This uses more than one dates
KostasHello amitchandak ,
thank you for the response and the article is really helpfull. Especialy the use relationship function that I didn't know about.
I understand that I need to create a date table and place the date value in the filter.
Also, I can see how I can take the count within the period that the user will select by connecting the two tables and use the USERELATIONSHIP function to create the measure but, how I will get the values before the period?
As in example one that I specified before, the perfect solution would be (Dateinfull column = Date column in date calendar table):
Open at Start of Period = CALCULATE(DISTINCTCOUNT([ID]), USERELATIONSHIP([Date1], 'Calendar'[Date in Full]) < 'Calendar'[Date in Full], ISBLANK([Date2]) || USERELATIONSHIP([Date2] , 'Calendar'[Date in Full]) > 'Calendar'[Date in Full])
But I cannot use the "userelationship" function in that way.What I could do is to calculate individually all measures and the create the parent measure that will give me the final results but,The issue is on how to return all dates for the specific relationship that occur before or after the calendar date.Could you explain me the process and the use please?Thanks- amitchandak
Kostas , Usage is wrong. Can you share sample in table format and expected output.
- Kostas
Hello amitchandak
Please find attached the table (it is the same as above).
I need to perform the calculations explained above and place them into a visual or individual cards.
The users need to be able to have a single date filter and to see the distinct count of ID's within the period that they will select.
The first example that mentioned is that I will need the distinct count of ID's that will meet two criteria:
1) The dates in the Date 1 column will be before the selected period (in the filter)
2) The dates in the Date 2 column will be either after the selected period or blank.
For Example if the user select the period 30 May to June 10 2020 the count should be 1 (ID = 4294)
I hope that make sense
ID Date1 Date2 4294 05 May 2020 04 June 2020 3392 30 May 2020 09 June 2020 4217 31 December 2020 10 January 2021 4216 31 December 2020 10 January 2021 4215 31 August 2020 10 September 2020 4214 31 August 2020 10 September 2020 4213 30 September 2020 10 October 2020 4217 31 December 2020 10 January 2021 4216 31 December 2020 10 January 2021 Thanks in advance
Kostas