Forum Discussion

Luis_Caston's avatar
Luis_Caston
Helper III
2 years ago

Power Query M calendar range

Dear all!
I've this calendar filter: Start date and End date.

I want to add columns according to a range date:

Option A: Winter cherry

  1. Imagine I've selected at the calendar filter:

     

    1. Start Date: 01/01/2.023
    2. End Date: 31/12/2.023
  2. I want to add a Column: Winter Cherry where the date range is:
    1. Start Date: 01/10/2.023
    2. End date: 31/03/2.024

I'm thinking in Power Query something like that but is not good:

Table.AddColumn(#"Changed Type2", "Winter Cherry", each if [NumMes] > 6 then """Winter Cherry""" else if [NumMes] < 3 then """Winter Cherry""" else null)
Calendarv2 


General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Luis_Caston ,

    Please try:

    (if [NumMes] >= 4 and [NumMes] <= 9 then "Summer Cherry" else "Winter Cherry")

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

  • Luis_Caston's avatar
    Luis_Caston
    Helper III
    2 years ago

    HI Anonymous !

     

    I add the pbix with the calendar: Calendarv2 
    Applying your code takes the months correctly but the year reange is Start date year 2.013 and End year 2.024

    How can I make 01/04/2.023 to 30/09/2.013 that is to say the same year of the start date.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Luis_Caston ,

      Consider creating an unconnect table and using its date column as a table field.

      And then create a new measure:

      Measure = 
VAR _selected_year = YEAR(MAX('Calendario'[Fecha]))
VAR _winter_cherry = SELECTEDVALUE('Calendario'[Winter Cherry])
VAR _cur_fecha = SELECTEDVALUE('Table'[Fecha])
VAR _count = CALCULATE(COUNTROWS('Calendario'),'Calendario'[Winter Cherry] = _winter_cherry && 'Calendario'[Fecha]=_cur_fecha)
VAR _filter = 
SWITCH(
    TRUE(),
    ISBLANK(_winter_cherry),IF(_cur_fecha>=MIN('Calendario'[Fecha])&&_cur_fecha<=MAX('Calendario'[Fecha]),1),
    _winter_cherry="Summer Cherry",IF(_cur_fecha>=DATE(_selected_year,4,1) && _cur_fecha <= DATE(_selected_year,9,30) && _count=1, 1),
    _winter_cherry="Winter Cherry",IF(_cur_fecha>=DATE(_selected_year,10,1) && _cur_fecha <= DATE(_selected_year+1,3,31) && _count=1, 1)
)
RETURN
_filter

      And use it as table visual's filter.

      Please check the pbix file.

      Best Regards,
      Gao
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
      If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

      How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

    • Luis_Caston's avatar
      Luis_Caston
      Helper III
      2 years ago

      Hi Anonymous !!

       

      How are you doing?
      The solution is perfect the unique thing I need is that when I select the period (winter cherry or summer cherry) instead of take the first year of the calendar as initial year takes the current year. That is to say:

      I add the pbix Calendarv2