Forum Discussion

ignaciokairuz's avatar
ignaciokairuz
Icon for Helper I rankHelper I
4 years ago
Solved

Create Pivot Table using multiple date columns of Recycling Bin

Howdy Folks?
 
Lets make an example of a situation :  I have the Recycle bin on Windows where there are multiple date columns that represent different attributes : creation date / last modification date / deletion date.
 
Now lets modify this typicall example and suppose that in this directory we have both files that have been deleted and others that have not those that have not been deleted have a null value in the correspondent row of the deletion date column.
 
Given this case I'm trying to create a pivot table that in the first column displays the ratio of deleted files over created files for a given month.

 

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    ignaciokairuz , You need created date column without time for these three column

     

    example

    DAX

    Date = [datetime].date
    or
    Date = date(year([datetime]),month([datetime]),day([datetime]))

    Power Query
    DateTime.Date([datetime])

     

    You need to join them with a common date column and two joins will be inactive, which you can activate using userelationship  in measure

    https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

    • ignaciokairuz's avatar
      ignaciokairuz
      Icon for Helper I rankHelper I
      4 years ago

      EDIT : Now I managed to understand! To those who , like me , can take a while to understand it, this is my way to paraphrase the solution : 

      Create a separate table with all the dates used in the principal table. Create a relationship using using Power BI / Power Query / Power Pivot ( I used Power Pivot ) between each of the 3 columns and the separate date table (2 of those columns should be inactive by deselecting the checkbox at the bottom left side of the dialogue box when creating said relationship). Then create a measure using this formula

       

      =DIVIDE(CALCULATE(COUNT(Tabla4_2[deleted]);USERELATIONSHIP(Tabla4_2[deleted];Tabla3[dates]));[Recuento de created])

       

      Hope it's easy to understand!

       

      Have a nice one!