Forum Discussion
Dynamic Mapping of Transaction Dates
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.
12 Replies
- Fowmy
Super User
Anonymous
Try this columnNew Column = IF( DAY(review[createddatetime]) > 21 , FORMAT( EOMONTH(review[createddatetime],1) , "Mmm YYYY"), FORMAT( review[createddatetime] , "Mmm YYYY") )- Fowmy
Anonymous
I did it on a sample dates table and it works, you may share a sample file to check:
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
Community Support
Hi Anonymous,
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!
