cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

measure help

hi there 

 

i need your support about to create a measure 

 

you can see below rule about that measure 

 

i have date column , the measure must work beased on that column : 

 

if the date column is current month it will write "Actual Month "

if the date column is before than current month  it will write  " Previous Month "

if the date column is after  than current month  it will write  " Next  Month "

 

sample : 

 

Current Month : September

 

01.10.2019--Previous Month

01.05.2020--Previous Month

01.08.2020-Previous Month

10.10.2020-Next Month

10.01.2021-NExt Month 

 

1 ACCEPTED SOLUTION

@erhan_79 

Measure = 
    VAR __Min = DATE(YEAR(TODAY()),MONTH(TODAY()),1)
    VAR __Max = EOMONTH(__Min,0)
RETURN
    SWITCH(TRUE(),
        MAX([Date])<__Min,"Previous Month",
        MAX([Date])>__Max,"Next Month",
        "Actual Month"
    )

But you can't use a measure in a slicer unless you use the disconnected table trick. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Super User IV
Super User IV

@erhan_79 , You can try like

Month Type = Switch( True(),
eomonth(Date[Date],0) = eomonth(Today(),0) ,"Current Month" , //This Month
eomonth(Date[Date],0) < eomonth(Today(),0) && ,"Previous Month" , //last Month
eomonth(Date[Date],0) > eomonth(Today(),0) && ,"Next Month" , //last Month
[Month Year]
)

OR

//Only one month

Month Type = Switch( True(),
eomonth(Date[Date],0) = eomonth(Today(),0) ,"Current Month" , //This Month
eomonth(Date[Date],0) = eomonth(Today(),-1) && ,"Previous Month" , //last Month
eomonth(Date[Date],0) =  eomonth(Today(),1) && ,"Next Month" , //last Month
[Month Year]
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

hi @amitchandak 

 

thanks for your reply but , when i write your formula after "emonth" system does not accept table name "date[date]" , system wants start date as a date 

@erhan_79 See attached PBIX below sig, Table (18)

Column = 
    VAR __Min = DATE(YEAR(TODAY()),MONTH(TODAY()),1)
    VAR __Max = EOMONTH(__Min,0)
RETURN
    SWITCH(TRUE(),
        [Date]<__Min,"Previous Month",
        [Date]>__Max,"Next Month",
        "Actual Month"
    )

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




thank you @Greg_Deckler  

 

but i want to create a mesure your sample is dax formula 

could you pls help about measure too

@erhan_79 

Measure = 
    VAR __Min = DATE(YEAR(TODAY()),MONTH(TODAY()),1)
    VAR __Max = EOMONTH(__Min,0)
RETURN
    SWITCH(TRUE(),
        MAX([Date])<__Min,"Previous Month",
        MAX([Date])>__Max,"Next Month",
        "Actual Month"
    )

But you can't use a measure in a slicer unless you use the disconnected table trick. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

thank you very much @Greg_Deckler 

 

it is working 

 

following you from tweeter too 🙂

 

i wish you success in your business always!you are very helpful

 

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

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

Top Solution Authors
Top Kudoed Authors