Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamically filter on current month - 1

Greeting lovely members . 

i have a simple power bi report that i want to make it Dynamic . 

My data base is simple , here is a sample :

let's say this is the table where it contains the data that i will use in my visuals , i have also a Date table and table for the teams . 

what's im trying to make here is a measure that will filter automatically on the current month - 1 . 

Ex : since we are in march , in one of my visual i want to show only the data of the current month - 1 which is Febrary . how can i acheive this .
also is it possible to set the YTD function dynamically ? stop the count at the current month-1 ?

 

Thanks all in advance .  

 

 

Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Here is the trick : 

    Current month test = if (FORMAT(DATEADD('Date'[Full date],1,MONTH),"mm yyyy")=FORMAT(TODAY(),"mm yyyy"),"true","false")
    Last year Current month test = if (FORMAT(DATEADD(DATEADD('Date'[Full date],1,YEAR),1,MONTH),"mm yyyy")=FORMAT(TODAY(),"mm yyyy"),"true","false")
     
    all i need to do is to filter my visuals based on these 2 columns and it solves the trick ğŸ˜Š
     
     

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, Anonymous 

    I hope I understand your question correctly.

     

    please try to follow the below.

    if it does not work, please let me know.

     

     

    yourmeasure Previous Month = CALCULATE( yourmeasure, DATEADD('Table'[Date], -1, MONTH))
     
    yourmeasure YTD until previous month =
    CALCULATE( yourmeasure, FILTER(ALLSELECTED('Table'[Date]), 'Table'[Date] <= EOMONTH(
    MAX('Table'[Date]) 
    , -1) && 'Table'[Year] = MAX('Table'[Year])))
     
    Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      First of all Thanks for you answer . 

       

      i think i find a way to solve it , i was thinking to add a column in my Date table called Previous Month that have values (True or false) and based on that column i filter my visuals , can u help me build that calculated columns . 

      i was able to create a column that return true or false for the current month using this Dax funtion : 

      Current month test = if (FORMAT('Date'[Full date],"mm yyyy")=FORMAT(TODAY(),"mm yyyy"),"true","false")
       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Here is the trick : 

        Current month test = if (FORMAT(DATEADD('Date'[Full date],1,MONTH),"mm yyyy")=FORMAT(TODAY(),"mm yyyy"),"true","false")
        Last year Current month test = if (FORMAT(DATEADD(DATEADD('Date'[Full date],1,YEAR),1,MONTH),"mm yyyy")=FORMAT(TODAY(),"mm yyyy"),"true","false")
         
        all i need to do is to filter my visuals based on these 2 columns and it solves the trick ğŸ˜Š