Dynamic Mapping of Transaction Dates

[JWick1969]

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.

This is what i have done so far (below code). the problem with my code if the january which the cutoff will start from Jan 1 to Jan 20 and also the December which will start from Nov 21 to Dec 31. How can I add this scenario to my code or is there any idea. 

January - Jan 1 to Jan 20  -- how to add this scenario in the current code.

Febuary - 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.

Create New Column

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

Any help greatly appreciated. 

Thank you.

[JWick1969]

Just created a cutoff table (please see attached) but encountering an error when i tried to do a relationship between cutoff table and the transaction table using monthyear column. The error say A circular defendency  was detected on transaction[sds_cutoff]. May I know where this error came from and what fixes should I do. 

This is the code I used to create a new column identifying the cutoff monthyear.

CutOff Month= 
               
CALCULATE (
    VALUES ( 'CutOffTable'[monthyear] ),
    FILTER (
        'CutoffTable',
        'CutoffTable'[From] <= EARLIER ( 'table'[createddatetime] )
            && 'CutoffTable'[To] >= EARLIER ( table[createddatetime] )
    )
)

cutoff table

[v-xulin-mstf]

Hi @JWick1969,

I can not reproduce your issue without data structure of 'table'.

Could you provide pbix file after removing sensitive information?

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[JWick1969]

May i know how can i upload a PBIX file?

[v-xulin-mstf]

Hi @JWick1969,

You can save the pbix file to a network drive and then share the public link.

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[JWick1969]

Hi,

I have this code and its working good. May I know your suggestion if I can use  a slicer to change from Normal calendar to SDS calendar?  How can i calculate a certain qty using both option? 

I Put this code under the date table.

Cutoff Month = 

SWITCH(Month(DateTable[Date])
, 12, FORMAT(date(DateTable[Year],MONTH(DateTable[Date]),1),"MMMM") -- & "-" & (DateTable[Year])
, FORMAT(date(DateTable[Year],IF(DAY(DateTable[Date])>=21,MONTH(DateTable[Date])+1,MONTH(DateTable[Date])),1),"MMMM") -- & "-" & (DateTable[Year])
)
        

[v-xulin-mstf]

Hi @JWick1969

Sorry, i'm not sure about your expected output, could you provide more details?

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[Fowmy]

@JWick1969 

Try this column

New Column = 

IF(
    DAY(review[createddatetime]) > 21 ,
    FORMAT( EOMONTH(review[createddatetime],1) , "Mmm YYYY"),
    FORMAT( review[createddatetime] , "Mmm YYYY")
)

[JWick1969]

Thank you for the reply.  The result is the same with my code.

[Fowmy]

@JWick1969 

I did it on a sample dates table and it works, you may share a sample file to check:

[JWick1969]

Thank you @Fowmy . This is what i'm looking for using a date table but could not figure out how can I add those setup in my date table. Currently, my PBI has DateTable. Could you please share the date table you are using.  Thank you in advance.

my DateTable

[Fowmy]

@JWick1969 

Your calendar table must have full years, you can modify it this way:

Calendar(
    date(year(table[r1_createdatetime]),1,1),
    date(year(table[r1_createdatetime]),12,31)
)

I have attached the PBIX file.

[JWick1969]

Hi, Thank you fo the reply. I have an error when i used the below code. did not found the table and the column. Based on the result of your sample I found inccorect mapping specially for december. The december should get the max because it will be an end year cut off the mapping should be nov 21 to dec 31.

Calendar(
    date(year(table[r1_createdatetime]),1,1),
    date(year(table[r1_createdatetime]),12,31)
)