Working with 5-4-4 calendars data

DAX DAX Calculations
1

Hi All,

Just wrote a dax formula with the help of internet, but is there a way i can show the data till Sep row only with ytd numbers ?

YTD Sales = 
    if(
        HASONEVALUE(Dates[FY]),
        CALCULATE([Charged Hours],
            FILTER(
                ALL(Dates),
                Dates[FY]=VALUES(Dates[FY])
                && Dates[Calendar Dates]<=MAX(Dates[Calendar Dates])
            )
        ),
    BLANK()
)

Also, i want to compare fy19 and fy20 ytd numbers (FY20 & FY19 - jul, aug, sep total). How should i proceed to write the dax to achieve this ? I have attached a snapshot for your reference.

powerbi

2

Hi nbaraili,

I want you check this one, and let me know if this one work out for you.

Chrs
Qasim

3

Hi @nbaraili,

I believe you already have [YTD Sales] Measure, so you can simply do below for FY20.

IF(SELECTEDVALUE(‘Date’[FY MonthName],BLANK()) IN {“Jul”,“Aug”,“Sep”},[YTD Sales],BLANK())

image

Please let me know if you are after something esle?

4

Hi @nbaraili, we’ve noticed that no response has been received from you since the 1st of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

5

Hello qasimjan1018,

This is really helpful.

6

Hello Hafiz,

Thank you for the reply.

Actually i have been looking for to compara the numbers for fy19 (Jul, aug, sep) and fy20 (jul, aug, sep).

Below is my approach: I calculate the max month from my current dataset.

Max MonthNum = CALCULATE(MAX(Dates[FY Month Number]),ALL(Dates))

After that, i calculated the current month num.

CurrMonthNo = SWITCH([Max Date],
“Jul”,1,“Aug”,2,“Sep”,3,“Oct”,4,“Nov”,5,“Dec”,6,“Jan”,7,“Feb”,8,“Mar”,9,“Apr”,10,“May”,11,“Jun”,12)

Finally, i calculated YTD numbers as follows:

YTD Across Years = CALCULATE([Charged Hours],FILTER(Dates,Dates[FY Month Number]<=[CurrMonthNo])).

Let me know if i can improve these steps.

7

Hi @nbaraili,

Good approach that you are trying to work out your solution. One problem can be resolved through different approaches. If I analyze your measures, I can see following issues.

  1. Why did you create CurrMonthNo? [Max MonthNum] is max month in current context and it is a number.

  2. [YTD Across Years] : This formula has 2 issues I believe. First, you did not put ALL(Dates) or ALLSELECTED(Dates) in FILTER, so it will only calculate for current month in context. Second, you are not specifying Year in Filter, so it will bring all months of all years. e.g If you are in November-2019 filter context, it will add all month(Jan-Oct) of all previous years.

Ideally, you should try to visualize your formula by putting it in table and see what’s working and what’s not working and why?

8

Hi @nbaraili,

By the way, if you want to compare 2018 & 2019 for July,Aug,Sep etc, you can easily do by following.

  1. Calculate YTD for 2018 & 2019 separately like YTD-2018 & YTD-2019.
  2. Use below formula and create 2 measures by replacing YTD-2018 & YTD-2019.

IF(SELECTEDVALUE(‘Date’[FY MonthName],BLANK()) IN {“Jul”,“Aug”,“Sep”},[YTD Sales],BLANK())

Please come back with your query if something does not work or you do not understand anything.

9

Hi , we’ve noticed that no response has been received from you since the 4th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

10

Hi @nbaraili, a similar topic was covered in this forum thread link. You might get additional tips here