cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Regular Visitor

## Dax help

Hi, power bi expert.

Need a help in calculating a column for 5 different flag as below.

I have a column of date range from year1960 to year2030 and from it i want to calculate a column which will have 5 falgs for 1-current month,2-previous month,3-previous-1 and 4- last year and the 5- No.

Where current will be the current month and then previous-1, previous -2 and previous -3 and the rest will be NO.

Suppose this is Feb 2023 so current flag will be Feb, previous will be Jan2023 , previous -1 will be dec2022 and last year will be nov 2022 and the rest should be no.

1 ACCEPTED SOLUTION
Super User

@Avinash_007 Not sure I am 100% on the requirements but try this. PBIX is attached below signature.

``````Column =
VAR __Date = [Date]
VAR __Today = TODAY()
VAR __CurrentEOM = EOMONTH(__Today,0)
VAR __CurrentBOM = DATE(YEAR(__CurrentEOM),MONTH(__CurrentEOM),1)
VAR __PreviousEOM = EOMONTH(__Today,-1)
VAR __PreviousBOM = DATE(YEAR(__PreviousEOM),MONTH(__PreviousEOM),1)
VAR __PreviousEOM1 = EOMONTH(__Today,-2)
VAR __PreviousBOM1 = DATE(YEAR(__PreviousEOM1),MONTH(__PreviousEOM1),1)
VAR __PreviousEOM2 = EOMONTH(__Today,-3)
VAR __PreviousBOM2 = DATE(YEAR(__PreviousEOM2),MONTH(__PreviousEOM2),1)
VAR __Result =
SWITCH(TRUE(),
__Date <= __CurrentEOM && __Date >= __CurrentBOM,"Current Month",
__Date <= __PreviousEOM && __Date >= __PreviousBOM,"Previous Month",
__Date <= __PreviousEOM1 && __Date >= __PreviousBOM1,"Previous Month -1",
__Date <= __PreviousEOM2 && __Date >= __PreviousBOM2,"Previous Month -2",
"NO"
)
RETURN
__Result``````

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Super User

@Avinash_007 Not sure I am 100% on the requirements but try this. PBIX is attached below signature.

``````Column =
VAR __Date = [Date]
VAR __Today = TODAY()
VAR __CurrentEOM = EOMONTH(__Today,0)
VAR __CurrentBOM = DATE(YEAR(__CurrentEOM),MONTH(__CurrentEOM),1)
VAR __PreviousEOM = EOMONTH(__Today,-1)
VAR __PreviousBOM = DATE(YEAR(__PreviousEOM),MONTH(__PreviousEOM),1)
VAR __PreviousEOM1 = EOMONTH(__Today,-2)
VAR __PreviousBOM1 = DATE(YEAR(__PreviousEOM1),MONTH(__PreviousEOM1),1)
VAR __PreviousEOM2 = EOMONTH(__Today,-3)
VAR __PreviousBOM2 = DATE(YEAR(__PreviousEOM2),MONTH(__PreviousEOM2),1)
VAR __Result =
SWITCH(TRUE(),
__Date <= __CurrentEOM && __Date >= __CurrentBOM,"Current Month",
__Date <= __PreviousEOM && __Date >= __PreviousBOM,"Previous Month",
__Date <= __PreviousEOM1 && __Date >= __PreviousBOM1,"Previous Month -1",
__Date <= __PreviousEOM2 && __Date >= __PreviousBOM2,"Previous Month -2",
"NO"
)
RETURN
__Result``````

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors