Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am trying to create a custom column containing custom date period (invoice period) as follow:
from 21th of a month to 20th of the next month.
I tried few things in dax but not able to get a proper result.
period = if(MONTH('Calendrier'[Date])=1;if(day('Calendrier'[Date])<21;1;(month('Calendrier'[Date])+1)); If (MONTH('Calendrier'[Date])>=1&&day('Calendrier'[Date])>20;(month('Calendrier'[Date])+1);if(MONTH('Calendrier'[Date])>=2&&day('Calendrier'[Date])<=21;month('Calendrier'[Date]))))
Kind regards,
Mohammad
Solved! Go to Solution.
My bad. It should have been
Period =
VAR __DATE =
DAY ( Table[Date] )
RETURN
IF (
__DATE <= 20,
FORMAT ( Table[Date], "mmm" ),
FORMAT ( EDATE ( Table[Date], 1 ), "mmm" )
)
or simply
Period =
RETURN
IF (
DAY ( Table[Date ) <= 20,
FORMAT ( Table[Date], "mmm" ),
FORMAT ( EDATE ( Table[Date], 1 ), "mmm" )
)
Proud to be a Super User!
Hi @mmp ,
Try this:
Period =
VAR __DATE =
DAY ( Table[Date] )
RETURN
IF (
DAY ( __DATE ) <= 20,
FORMAT ( __DATE, "mmm" ),
FORMAT ( EDATE ( __DATE, 1 ), "mmm" )
)
Replace the table and column names with the actual ones.
Proud to be a Super User!
@danextian Thank you for your reply.
Your solution does not work properly, it gives me some month mixed up.
This is what i implemented but it returns null for the last value of my calendar because it does not contains the required value to calculate for the month ahead.
Mois Fct = SWITCH (
TRUE ();
DAY(Calendrier[Date])>20;FORMAT(DATEADD(Calendrier[Date];1;MONTH);"mmm");FORMAT(Calendrier[Date];"mmm")
)
Kind regards,
Mohammad
My bad. It should have been
Period =
VAR __DATE =
DAY ( Table[Date] )
RETURN
IF (
__DATE <= 20,
FORMAT ( Table[Date], "mmm" ),
FORMAT ( EDATE ( Table[Date], 1 ), "mmm" )
)
or simply
Period =
RETURN
IF (
DAY ( Table[Date ) <= 20,
FORMAT ( Table[Date], "mmm" ),
FORMAT ( EDATE ( Table[Date], 1 ), "mmm" )
)
Proud to be a Super User!
@mmp Please try this
Column 2 = SWITCH(TRUE()
,DAY('Calendar'[Date])<21,FORMAT('Calendar'[Date],"MMM")
,FORMAT(DATEADD('Calendar'[Date].[Date],1,MONTH),"MMM"))
or else try this one
Column = FORMAT(DATEADD('Calendar'[Date].[Date],11,DAY),"MMM")
this will also consider the number of days in month
@mmp Hope I understood it clearly. So your month should start from 21st. Please try dateadd function
Column = DATEADD('Calendar'[Date].[Date],-20,DAY)
Thank you for your reply @Anonymous.
What I want to achieve is more like this:
Date Month Invoice (custom column)
01/01/19 jan
02/01/19 jan
03/01/19 jan
04/01/19 jan
05/01/19 jan
06/01/19 jan
07/01/19 jan
08/01/19 jan
09/01/19 jan
10/01/19 jan
11/01/19 jan
12/01/19 jan
13/01/19 jan
14/01/19 jan
15/01/19 jan
16/01/19 jan
17/01/19 jan
18/01/19 jan
19/01/19 jan
20/01/19 jan
21/01/19 feb
22/01/19 feb
23/01/19 feb
24/01/19 feb
25/01/19 feb
26/01/19 feb
27/01/19 feb
28/01/19 feb
29/01/19 feb
30/01/19 feb
31/01/19 feb
01/02/19 feb
02/02/19 feb
03/02/19 feb
04/02/19 feb
05/02/19 feb
06/02/19 feb
07/02/19 feb
08/02/19 feb
09/02/19 feb
10/02/19 feb
11/02/19 feb
12/02/19 feb
13/02/19 feb
14/02/19 feb
15/02/19 feb
16/02/19 feb
17/02/19 feb
18/02/19 feb
19/02/19 feb
20/02/19 feb
21/02/19 mar
22/02/19 mar
23/02/19 mar
24/02/19 mar
25/02/19 mar
26/02/19 mar
27/02/19 mar
28/02/19 mar
01/03/19 mar
02/03/19 mar
03/03/19 mar
04/03/19 mar
05/03/19 mar
06/03/19 mar
07/03/19 mar
08/03/19 mar
09/03/19 mar
10/03/19 mar
11/03/19 mar
12/03/19 mar
13/03/19 mar
14/03/19 mar
15/03/19 mar
16/03/19 mar
17/03/19 mar
18/03/19 mar
19/03/19 mar
20/03/19 mar
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |