Power Apps Exchange

Please login or click SIGN UP FOR FREE to create your PowerAppsUG account to join this user group.
 View Only

DAX: Dynamic mapping of Transaction date

  • 1.  DAX: Dynamic mapping of Transaction date

    Bronze Contributor
    Posted Aug 03, 2021 10:56 PM
    Edited by Jon Olarte Aug 04, 2021 02:19 AM
      |   view attached
    Hi All,

    Can someone please help me to find a solution to map the transaction date using this scenario.
    Transaction monthly 
    cutoff (21st date Previous month to 20th Current month. but i have a different scenario for January and December. below is the code I'm using right now. any hep is very much appreciated. thank you in advance.

    create New Column
    
    Calendar_Month = FORMAT(date(table[Year],IF(DAY(table[createddatetime])>=22,MONTH(table[createddatetime])+1,MONTH(table[createddatetime])),1),"MMM") & "-" & (table[Year]

    Cutoff Date

    January - Jan 1 to Jan 20  -- considered mark as January.
    February - Jan 21 to Feb 20
    March - Feb 21 to Mar 20
    April - Mar 21 to Apr 20
    May -  Apr 21 to May 20
    June - May 21 to June 20
    July -  June 21 to July 20
    August -  July 21 to Aug 20
    September - Aug 21 to Sep 20
    October - Sept 21 to Oct 20
    November - Oct 21 to Nov 20
    December - Nov 21 to Dec 31  -- -- how to add this scenario in the current code.

    crossed to year 2021
    January - Jan 1 to Jan 20
    February - Jan 21 to Feb 21



    ------------------------------
    Jon Olarte
    BI Developer
    ------------------------------

    Attachment(s)

    pbix
    sample data.pbix   46 KB 1 version