Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a column with dates in the following format:
d/m/yyyy
How can I create a calculated column that has as values true for every entry that is on the previous month and false for the rest?
I tried the following but it didn't work.
=IF([Date]=EDATE(DATE(YEAR(NOW());MONTH(NOW()));-1);TRUE();FALSE())
I know I must be close but how can I implement the day to my formula?
Solved! Go to Solution.
@mork This should work...
Column = IF ( YEAR ( Calendar[Date] ) = YEAR ( NOW () ) && MONTH ( Calendar[Date] ) = MONTH ( NOW () ) - 1, TRUE (), FALSE () )
The posted soltion does not work over multiple years:
Use:
Hi,
Thank you for help !!! this is work !
But I want to do the same thing, but on the 3 last month (included the last year). I test many things but doesn't work. Can you help me ?
Thank you ! 🙂
@mork This should work...
Column = IF ( YEAR ( Calendar[Date] ) = YEAR ( NOW () ) && MONTH ( Calendar[Date] ) = MONTH ( NOW () ) - 1, TRUE (), FALSE () )
Hi Mork,
This should do the trick:
=IF(STARTOFMONTH(YourTable[YourDateField]) = date(year(TODAY()); MONTH(today())-1; 1) ;TRUE();FALSE())
HTH,
@mork I'm not sure about this. bt try this it may work
if(month(date)=month(date)-1,"True","False")
@mork This should work for you however in your dateColumn you will only need date that is not greater than today.
NewColumn = IF( DATEDIFF('YOURTABLE'[dateColumn],TODAY(),DAY) <= 30, "Yes", "No")
Unfortunately that's is not what I want. Your formula is about the previous 30 days and not about the previous month. So for example today is the 13th of May. The data that I will see won't be the data of the previous month but will be the data of the last 30 days. Meaning half of April and half of May.
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |