Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mmp
Helper I
Helper I

Custom period for calendar date

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

1 ACCEPTED 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" )
    )





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

7 REPLIES 7
danextian
Super User
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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

@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" )
    )





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Thank you @danextian,

 

Your solution is so far better than mine !

 

Kind regards,

 

Mohammad

Anonymous
Not applicable

@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

Anonymous
Not applicable

@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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.