Forum Discussion

ClaireBear's avatar
ClaireBear
Helper I
6 years ago
Solved

Previous Month Calculated Column not working for January

Hello,    I have a calculated column that flags previous month entries. The issue im having is that now in January, the calculation does not seem to work. Last year this was working beautifully.  ...
Need Help
  • Gordonlilj's avatar
    Gordonlilj
    6 years ago

    The Quarter function came with the 2019 december update.

    You could use Roundup instead

     

    IsPreviousQuarter = 

IF (
 YEAR ('Fact Sales'[TransactionDateTime].[Date]) & 
 ROUNDUP(MONTH('Fact Sales'[TransactionDateTime].[Date])/3,0) = 
  FORMAT(DATE( YEAR(TODAY()),
               ROUNDUP(MONTH(TODAY())/3,0)-1,
               DAY(TODAY())),"YYYYQ"),
"Yes",
"No"
)
Gordonlilj's avatar
Gordonlilj
Solution Sage
6 years ago

Hi,

 

I'm not sure if this is what you're after but you could try and change the code to something like below

IsPreviousMonth = 

IF (
 YEAR ('Fact Sales'[TransactionDateTime].[Date]) & 
 MONTH( 'Fact Sales'[TransactionDateTime].[Date]) = 
 FORMAT(DATE( YEAR(TODAY()),
              MONTH(TODAY())-1,DAY(
              TODAY())),"YYYYM"),
"Yes",
"No"
)

 

  • ClaireBear's avatar
    ClaireBear
    Helper I
    6 years ago

    Yes, thank you. It is working. 

    I now have to figure out how to do this for Quarters.

    • Gordonlilj's avatar
      Gordonlilj
      Solution Sage
      6 years ago

      If its the previous quarter you're after then you could probably replace MONTH with QUARTER like below

       

       

      IsPreviousQuarter = 

IF (
 YEAR ('Fact Sales'[TransactionDateTime].[Date]) & 
 QUARTER( 'Fact Sales'[TransactionDateTime].[Date]) = 
  FORMAT(DATE( YEAR(TODAY()),
               QUARTER(TODAY())-1,
               DAY(TODAY())),"YYYYQ"),
"Yes",
"No"
)

       

       

       

      • ClaireBear's avatar
        ClaireBear
        Helper I
        6 years ago

        Thank you! Unfortunately there is no calculation for Quarter.