Multiple Date Column slicer

[nmeliasp]

Hello i am relatively new to PowerBI and need some help

I have 3 columns that have dates in them simillar to example below. How can i create One slicer to see every completion in October for example? 

X completion	Y Completion	Z Completion
10/1/2018	11/1/2018	10/15/2018

[AlB]

Hi @nmeliasp

You could create a Calendar table with a relationship for example to Table[X Completion].

The you create a simple measure:

[Completions]= COUNT(Table[X Completion])

Use a slicer with the month column from Calendar and  select the month you want.   

[nmeliasp]

i would like to specify a month for example October and see completions from X Completion Column and Z Completion Column. Not sure if your solution willl work for that.

[AlB]

Let's see if I understand what you're after. In the table you've depicted, what would be the result then for October, 2? OR do you want separate results for each column X, Y, Z?

[nmeliasp]

i would like to seperate the results for each column

[AlB]

Ok,  you can have one measure for each column following what I described earlier although that would imply having

a relationship between Calendar and your data table for each column, which is cumbersome if the number of columns is high. Additionally, you would need to use USERELATIONSHIP to activate the relationships as in:

[Completions X]= CALCULATE(COUNT(Table[X Completion]),

                                             USERELATIONSHIP(Table[X Completion],Calendar[Date]))    

In any case, the way your table is organised is not the most convenient. Probably best to rearrange it in a more "processable" way so that you have something like:

Date                 Type_of_Completion 

10/01/18                   X

11/01/18                   Y

10/15/18                   Z

You can do this easily in the query editor with the Pivot and Unpivot operations. 

With this table you would create a sole relationship between Calendar[Date] and Table[Date] and then you can use the measure

COUNT(Table[Type_of_Completion ])

with [Type_of_Completion] in rows of your matrix and the slicer for the month. Make sense?

[nmeliasp]

This is a great start thank you. Now my next task is being able to measure the differences between these columns. is there a way to have an unpivoted version and a pivoted column? I find measuring the difference between te different completion columns is easier when each completion type is a column unless i'm missing something

[AlB]

@nmeliasp

Well, all the info for calculating the differences is in what we did before. I don't know how you want to show it.  

You could, if the number of types of completions is low, have hard-coded measures like:

[Compl_X]= CALCULATE(COUNT(Table[Type_of_Completion ]);Table[Type_of_Completion="X")   

[Compl_Y]=CALCULATE(COUNT(Table[Type_of_Completion ]);Table[Type_of_Completion="Y")

and then create other measures simply with the difference.

Another option, although probably overly complicated for what you need, would be to add an additional column to the table shown earlier that's just a copy of Type_of_Completion:

Date                 Type_of_Completion      Type_of_Completion_Filter

10/01/18                   X                                          X

11/01/18                   Y                                          Y

10/15/18                   Z                                          Z    

You can then have have Type_of_Completion in rows of the matrix, Type_of_Completion_Filter in a slicer and by selecting one of X, Y or Z on the slicer you choose which one you ant to subtract from those in the rows. This would require a measure like this

Measure_Diff =
CALCULATE (
    COUNT ( Table[Type_of_Completion] ),
    ALL ( Table[Type_of_Completion_Filter] )
)
    - CALCULATE (
        COUNT ( Table[Type_of_Completion_Filter] ),
        ALL ( Table[Type_of_Completion] )
    )

[Ashish_Mathur]

Hi,

Share some data and also show the expected result.

[nmeliasp]

I need to calculate the number of days between the different completion types

Ffor example:

Z Completion - X Completion

10/15/2018 - 10/1/2018

goal is to measure cycle times for these three completion types

[AlB]

You need to be less ambiguous describing what you want. I do not understand it.

A sample of the data model would also be helpful.

[nmeliasp]

Here is an example of what i am trying to calculate. This would be a DATEDIFF between Completion X and Completion Y, however these columns were melted into an attribute->value columns when i did an unpivot action on them. Hope this helps...

Completion X	Completion Y	Completion Z	Cycle Time X-Y	Cycle Time Y-Z
10/1/2018	11/1/2018	12/1/2018	31	30
10/2/2018	11/12/2018	12/2/2018	41	20
10/3/2018	11/3/2018	12/3/2018	31	30
10/4/2018	11/14/2018	12/4/2018	41	20
10/5/2018	11/5/2018	12/4/2018	31	29
10/6/2018	11/16/2018	12/4/2018	41	18
10/7/2018	11/7/2018	12/4/2018	31	27

[AlB]

So you already have it. A DATEDIFF and that is it. What is the problem?

[nmeliasp]

my problem now is i did an unpivot action and now my columns look like this

Completion Type	Completion Date
Completion X	10/1/2018
Completion Y	11/1/2018
Completion Z	12/1/2018

I am trying to pivot these columns back but having trouble retainig the values. Is there a different way to calcuate the DATEDIFFs between these completion types withouth having to pivot the columns back?

[v-chuncz-msft · Accepted solution]

@nmeliasp,

You may try using CALCULATE Function to change the context.

https://www.sqlbi.com/articles/filter-arguments-in-calculate/