Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to calculate Dynamic quarters

Hello guys I have to calculate the dynamics QUATERS  I mean now this is Sept month means Q2  so  want to show the values of Q2 and Q1 also in the next month the quarter  will change then it will show...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Anonymous 

    try this, then put the measure into visual level filter

    filterMeasure = 
VAR _currentMonth = 7 //replace it with month(today())
VAR _currentYear = 2022//replace it with year(today())
VAR _quarter =
    DIVIDE ( _currentMonth, 3 )
VAR _day =
    _quarter - QUOTIENT ( _currentMonth, 3 )
VAR _startDate =
    IF (
        _currentMonth < 4,
        DATE ( _currentYear - 1, 1, 1 ),
        DATE ( _currentYear, 1, 1 )
    )
VAR _endDate =
    SWITCH (
        TRUE (),
        _currentMonth < 4, DATE ( _currentYear - 1, 12, 31 ),
        _day <> 0,
            DATE ( _currentYear, INT ( _quarter ) * 3 + 1, 1 ),
        _day = 0,
            DATE ( _currentYear, ( _quarter - 1 ) * 3 + 1, 1 )
    )
RETURN
    IF(MIN('value'[date])<_endDate&&MIN('value'[date])>=_startDate,1,0)

    2021/10

    2022/1

    2022/7

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

v-xiaotang's avatar
v-xiaotang
Icon for Community Support rankCommunity Support
4 years ago

Hi Anonymous 

you can refer to the posts,

https://community.powerbi.com/t5/Desktop/Dynamically-display-last-8-quarters/td-p/394240

https://community.powerbi.com/t5/Desktop/Display-last-4-quarters-sum-dynamically/td-p/1045885

https://community.powerbi.com/t5/Desktop/Current-Quarter-and-last-quarter-calculation/td-p/703345

 

if you need more help, please @ me

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks for your Reply

    I have to show the quaters in columns like Q1 ,Q2,Q3,Q4 now it's oct month means it comes in Q3 so my matrix will show  Q1 column ,Q2 column & Q3  column  and when Jan month will Start matrix will show Q1,Q2,Q3 Q4 ...and after that when new FY will Start i have to show the only Q1 of this year.

    • v-xiaotang's avatar
      v-xiaotang
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi Anonymous 

      try this, then put the measure into visual level filter

      filterMeasure = 
VAR _currentMonth = 7 //replace it with month(today())
VAR _currentYear = 2022//replace it with year(today())
VAR _quarter =
    DIVIDE ( _currentMonth, 3 )
VAR _day =
    _quarter - QUOTIENT ( _currentMonth, 3 )
VAR _startDate =
    IF (
        _currentMonth < 4,
        DATE ( _currentYear - 1, 1, 1 ),
        DATE ( _currentYear, 1, 1 )
    )
VAR _endDate =
    SWITCH (
        TRUE (),
        _currentMonth < 4, DATE ( _currentYear - 1, 12, 31 ),
        _day <> 0,
            DATE ( _currentYear, INT ( _quarter ) * 3 + 1, 1 ),
        _day = 0,
            DATE ( _currentYear, ( _quarter - 1 ) * 3 + 1, 1 )
    )
RETURN
    IF(MIN('value'[date])<_endDate&&MIN('value'[date])>=_startDate,1,0)

      2021/10

      2022/1

      2022/7

      Best Regards,

      Community Support Team _Tang

      If this post helps, please consider Accept it as the solution to help the other members find it more quickly.