topic Re: Calculate date filter in DAX Commands and Tips

Calculate date filter

Anonymous — Sat, 24 Dec 2022 19:45:42 GMT

Hello,

I ve been trying to improve my calculation with additional date filter, but it doesnt make any diff. It behaves like it calculates the result for all dates

Everything is calculates in one table. measure reacts correctly to date slicer or filter visual modification, but only not for dax...

Aby idea what can be wrong?

Re: Calculate date filter

tamerj1 — Sat, 24 Dec 2022 20:06:37 GMT

Hi Anonymous

bring CALCULATE at line number 2 instead of 5

Re: Calculate date filter

Anonymous — Sat, 24 Dec 2022 20:53:32 GMT

Could you elaborate more? You mean putting sumx as VAR?

Yield Mech. assembly =

VAR nom =

CALCULATE( SUMX(TJob, TJob[Antal]), TJob[SekundærNavn] = "Mech. assembly" , PREVIOUSMONTH(TJob[Dato]))

VAR den =

CALCULATE(SUMX(TJob, TJob[FirstPassQty]), TJob[SekundærNavn] = "Mech. assembly", PREVIOUSMONTH(TJob[Dato]))

RETURN

ROUND(

DIVIDE (

den,

nom),

Re: Calculate date filter

tamerj1 — Sat, 24 Dec 2022 21:07:27 GMT

Anonymous

Please try

Yield Mech. assembly111 =
CALCULATE (
VAR MechAssembly =
FILTER ( TJob, TJob[SekundarNavn] = "Mech. assembly" )
RETURN
ROUND (
DIVIDE (
SUMX ( MechAssembly, TJob[Antal] ),
SUMX ( MechAssembly, TJob[FirstPassQty] )
),
4
),
PREVIOUSMONTH ( TJob[Dato] )
)

Re: Calculate date filter

Anonymous — Sat, 24 Dec 2022 22:06:34 GMT

Idk know why but the measure keeps displaying blank after previous month function. So as few others i had try for example

Date column is correctly formatted as date, rest as whole number.

Re: Calculate date filter

Anonymous — Sun, 25 Dec 2022 13:20:40 GMT

https://drive.google.com/file/d/1Ry9C5zrcUi59CV6j3GqAYjngYlr3wIYg/view?usp=sharing

Link for contoso model, same problem with different example

Re: Calculate date filter

Anonymous — Tue, 27 Dec 2022 07:40:53 GMT

Hi Anonymous ,

Please try this measure:

(Try PARALLELPERIOD(TJob[Dato],-1,MONTH) instead of PREVIOUSMONTH(TJob[Dato]))

PrevMonth = CALCULATE(SUMX(Sales, Sales[Unit Cost]), PARALLELPERIOD('Date'[Date],-1,MONTH))

Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Re: Calculate date filter

Anonymous — Tue, 27 Dec 2022 06:47:13 GMT

Anonymous Calculated you presented isnt filtering any date. Its result of sumx with function all to each month? Could you try to download contoso file. Maybe my settings for file are disrupting the calculation... I m running out of ideas

https://static1.squarespace.com/static/5c3a30e0f7939271ddaa7e4f/t/63a0b153266500608e08b098/1671475547070/ContosoDBGenerator-PowerBIDemo.pbix

Re: Calculate date filter

Anonymous — Tue, 27 Dec 2022 07:08:23 GMT

ref

Sample of my file aatached also

https://drive.google.com/file/d/1fPAhnB2t2XWQKODOxKsEXRwD-_44Cm6r/view?usp=sharing

Re: Calculate date filter

tamerj1 — Tue, 27 Dec 2022 10:41:54 GMT

Anonymous
Does this work for you?

Re: Calculate date filter

Anonymous — Tue, 27 Dec 2022 11:24:17 GMT

Could you reference measure to VAR version. Contoso model looks very good, refering to year month number is pretty nifty idea, but when i tried to copy results....

It still wont give me the previous month

Re: Calculate date filter

tamerj1 — Tue, 27 Dec 2022 11:33:48 GMT

Anonymous
Can you copy/paste this code so I can modify?

Re: Calculate date filter

Anonymous — Tue, 27 Dec 2022 11:37:31 GMT

Yield Mech. assemblyprevmonth =

VAR MechAssembly =

FILTER ( TJob, TJob[SekundærNavn] = "Mech. assembly" )

RETURN

CALCULATE(ROUND(

DIVIDE (

SUMX ( MechAssembly, TJob[FirstPassQty] ),

SUMX ( MechAssembly, TJob[Antal] ) ),

4),

ALL('Date'),

'Date'[Year Month Number] = MAX('Date'[Year Month Number]) -1)

Re: Calculate date filter

tamerj1 — Tue, 27 Dec 2022 11:40:45 GMT

Anonymous
Please try

Yield Mech. assemblyprevmonth = VAR CurrentYearMonth = MAX ( 'Date'[Year Month Number] ) VAR MechAssembly = CALCULATETABLE ( TJob, TJob[SekundærNavn] = "Mech. assembly", 'Date'[Year Month Number] = CurrentYearMonth - 1, ALL ( 'Date' ) ) RETURN ROUND ( DIVIDE ( SUMX ( MechAssembly, TJob[FirstPassQty] ), SUMX ( MechAssembly, TJob[Antal] ) ), 4 )

Re: Calculate date filter

Anonymous — Tue, 27 Dec 2022 11:46:09 GMT

You re a God!

The magic question what is the purpose of previousmonth function if its not working... or why is it not working

Re: Calculate date filter

tamerj1 — Tue, 27 Dec 2022 12:04:18 GMT

Anonymous
It should work but I usually don't like to use time intellagence functions. You may try

Yield Mech. assemblyprevmonth = VAR CurrentYearMonth = MAX ( 'Date'[Year Month Number] ) VAR MechAssembly = CALCULATETABLE ( TJob, TJob[SekundærNavn] = "Mech. assembly", PREVIOUSMONTH ( 'Date'[Date] ) ) RETURN ROUND ( DIVIDE ( SUMX ( MechAssembly, TJob[FirstPassQty] ), SUMX ( MechAssembly, TJob[Antal] ) ), 4 )

Re: Calculate date filter

tamerj1 — Tue, 27 Dec 2022 12:07:55 GMT

Anonymous
Forgot to mention that it returns blank if the date table is not filtered i.e. at the grand total level.

Re: Calculate date filter

Anonymous — Tue, 27 Dec 2022 12:15:11 GMT

And it returns blank. Well it doesnt matter right now. Thank you.

To bad dax guides for time intelligence are showing easy results, nothing real life databases. At least for me