DAX Formula to get number of days between two dates?

%3CLINGO-SUB%20id%3D%22lingo-sub-1823370%22%20slang%3D%22en-US%22%3EDAX%20Formula%20to%20get%20number%20of%20days%20between%20two%20dates%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1823370%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20all.%20I%20have%20developed%20a%20power%20pivot%20and%20I%20am%20seeking%20to%20find%20the%20number%20of%20days%20between%20two%20dates.%20I%20have%20tried%20DATEDIFF%20unsuccessfully.%20Any%20hints%2Ftips%20on%20how%20to%20calculate%20this%3F%20On%20the%20screenshot%20below%20I%20am%20seeking%20to%20get%20the%20number%20of%20days%20between%20the%20%22BeginHarvestDate%22%20and%20the%20%22PlantDate%22.%20Thanks%20for%20any%20help!%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(2).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F229540i21E07A45F4E3227B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(2).png%22%20alt%3D%22Screenshot%20(2).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1823370%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1823498%22%20slang%3D%22en-US%22%3ERe%3A%20DAX%20Formula%20to%20get%20number%20of%20days%20between%20two%20dates%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1823498%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676026%22%20target%3D%22_blank%22%3E%40slohombre%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20simply%20try%20this...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ENumber%20of%20Days%3A%3DDATEDIFF(DaysToHarvest%5Bplantdate%5D%2C%5Bbeginharvestdate%5D%2CDAY)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1824841%22%20slang%3D%22en-US%22%3ERe%3A%20DAX%20Formula%20to%20get%20number%20of%20days%20between%20two%20dates%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1824841%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you.%20I%20will%20execute%20this%20in%20Power%20Query%20-%20thanks%20for%20that%20advice.%20I%20am%20having%20trouble%20with%20your%20formula%20in%20the%20power%20query%20editor.%20I%20used%3A%26nbsp%3B%20Duration.Days(%23duration%5Bbeginharvestdate%5D-%5Bplantdate%5D)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20formula%20is%20yielding%20an%20expression%20error%3A%20We%20cannot%20apply%20field%20access%20to%20the%20type%20function.%20Details%3A%3C%2FP%3E%3CP%3EValue%3D%5BFunction%5D%3C%2FP%3E%3CP%3EKey%3Dbeginharvestdate%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20thoughts%20on%20what%20I%20am%20doing%20wrong%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi, all. I have developed a power pivot and I am seeking to find the number of days between two dates. I have tried DATEDIFF unsuccessfully. Any hints/tips on how to calculate this? On the screenshot below I am seeking to get the number of days between the "BeginHarvestDate" and the "PlantDate". Thanks for any help!Screenshot (2).png

5 Replies
Highlighted

@slohombre 

You may simply try this...

Number of Days:=DATEDIFF(DaysToHarvest[plantdate],[beginharvestdate],DAY)
Highlighted

@slohombre 

As variant in DAX that could be as well

=INT(Table1[End]-Table1[Start])

but why don't you do this transformation in Power Query since the table generated in it. That's like

..., each Duration.Days(Duration.From([End]-[Start]))

In general it's better to avoid calculated columns generated by DAX in data model if you can do them in Power Query.

Highlighted

@Sergei Baklan Thank you. I will execute this in Power Query - thanks for that advice. I am having trouble with your formula in the power query editor. I used:  Duration.Days(#duration[beginharvestdate]-[plantdate]) 

 

That formula is yielding an expression error: We cannot apply field access to the type function. Details:

Value=[Function]

Key=beginharvestdate

 

Any thoughts on what I am doing wrong?

Highlighted

@slohombre 

If you wish to do it in Power Query, refer to the following screenshot.

 

Number of days.jpg

Highlighted

@Subodh_Tiwari_sktneer Thank you, Sir. Very helpful.