Two date field to one date field

[DavidSSAA]

Hi,

I have two date field one is for the registration date and one for the date customer made his first time deposit.

I wonder if it possible to have one date field that present what acutal happend in this specific date in terms of how many customers made registration and how many made First deposit.

for exmaple:

ID   RegDate   FtdDate   Reg   Ftds

1    1/1/2016                      1     

2    1/1/2016    1/2/2106    1        1

3    1/2/2016    1/2/2016    1        1

So if i want to know what happend in the 1/2/2016 i will get that

Date         Reg    Ftds

1/2/2016    1        2

Thanks for any help

[Baskar]

Cool dude,

Here is the solution for your doubt.

https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

let me know if it is not clear. then i will help u 

[ImkeF]

If you prefer, you can do this without any DAX. Just paste this into the advanced editor in the query editor and replace the Source-step with the reference to your table/query:

let
 
 Source=Table.FromRecords(
 {
 [ID= 1, RegDate= "2016/01/01", FtdDate="",Reg=1,Ftds=""],
 [ID= 2, RegDate= "2016/01/01", FtdDate="2016/02/01",Reg=1,Ftds=1],
 [ID= 3, RegDate= "2016/02/01", FtdDate="2016/02/01",Reg=1,Ftds=1]
 }
 ),
    GroupedRegs = Table.Group(Source, {"RegDate"}, {{"Reg", each List.Sum([Reg]), type number}}),
    Reg = Table.RemoveRowsWithErrors(GroupedRegs, {"Reg"}),
    GroupedFtds = Table.Group(Source, {"FtdDate"}, {{"Ftds", each List.Sum([Ftds]), type number}}),
    Ftds = Table.RemoveRowsWithErrors(GroupedFtds, {"Ftds"}),
    #"Merged Queries" = Table.NestedJoin(Reg,{"RegDate"},Ftds,{"FtdDate"},"NewColumn",JoinKind.FullOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Ftds"}, {"Ftds"})
in
    #"Expanded NewColumn"

[MFelix]

Hi @DavidSSAA,

I think you can do this with a relationship, I have copied your information to a PBIX file and named the table information and dates.

Table dates is only a simple table of calendar I started it in 01/01/2016 until end 2016 but you can have it with much longer dates, in all my reports that needs dates I think it's always a useful table to have.

Create two relationships (image 1):

- Information (FtdDate) to Dates (Date) - Active relationship

- Information (RegDate) to Dates (Date) - not active

Create a measure on the information table:

- Reg_calc = CALCULATE(SUM(Information[Reg]),USERELATIONSHIP(Dates[Date],Information[RegDate]))

(USERRELATIONSHIP allows you to "activate" relationship that are made but not active so we can used then in our measures)

Then use the Dates table to make the date filters and the Reg_calc and Ftds to make the graphics

As you can see in image 2 and 3 if you use the normal Reg field the information is incorrect (image 2) if you use Reg_calc  the information should give what you need (image 3).

If the active relationship is the RegDate and Ftddate is inactive the field to use in the measure is Ftd and not Reg.

I only did it with the information you placed in your post but I think this should work.

Regards

Mfelix

[Greg_Deckler]

You could use two slicers or two filters one for RegDate and one for FtdDate and set them both to the same value...

[DavidSSAA]

if i do so and fillter it to specific Reg date i will get only customers that register in this specific date and made ftds.

In general i wish to see column Date that tell me what happend in each date.

Hope there is some way to do it more cleaner, may in the scheme part.

Thanks

[Eric_Zhang · Accepted solution]

@DavidSSAA

Then you'll have to re-model the table and create measures.

ftd = SUMX(FILTER(Table1,Table1[type]="ftd"),Table1[Val])

reg = SUMX(FILTER(Table1,Table1[type]="reg"),Table1[Val])

let
 
 Source=Table.FromRecords(
 {
 [ID= 1, RegDate= "2016/01/01", FtdDate="",Reg=1,Ftds=""],
 [ID= 2, RegDate= "2016/01/01", FtdDate="2016/02/01",Reg=1,Ftds=1],
 [ID= 3, RegDate= "2016/02/01", FtdDate="2016/02/01",Reg=1,Ftds=1]
 }
 ),


 RegActions=Table.SelectColumns(Source, {"ID","RegDate","Reg"}),
 FtdActions=Table.SelectColumns(Source, {"ID","FtdDate","Ftds"}),
 RegActionsAddCol=Table.AddColumn(RegActions,"type",each "reg"), 
 FtdActionsAddCol=Table.AddColumn(FtdActions,"type",each "ftd"),
 RenameRegActionsColumns = Table.RenameColumns(RegActionsAddCol,{{"Reg", "Val"}, {"RegDate", "Date"}}), 
 RenameFtdActionsColumns = Table.RenameColumns(FtdActionsAddCol,{{"Ftds", "Val"}, {"FtdDate", "Date"}}),
 UnionActions= Table.Combine({RenameRegActionsColumns , RenameFtdActionsColumns }),
 #"Filtered Rows" = Table.SelectRows(UnionActions, each ([Val] = 1)),
 #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}, {"Val", Int64.Type}})
 
in
 #"Changed Type"