Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Magellan2022
Helper I
Helper I

Current Year and Prev Year by Week of Month Based On Fiscal Calendars

Hello,

 

The below line graph behavior is what I'm trying to re-create in Power BI when no date slicer values are selected. The graph has a Current Year line(blue) for gross profit and a second line(orange) for Previous Year gross profit both on the y-axis with Week of Month on the x-axis. The Dim Calendar being used is a fiscal calendar that can vary between different organizations and what their fiscal calendar looks like(e.g 4-4-5 calendar, 4-5-4 calendar, etc). For instance, Org. A might have fiscal calendar that contains 4 weeks for May 2022 while Org. B has 5 weeks for May 2022. 

 

I want the Data Effective Date that's unique to each Org. to drive the gross profit calculation for the Current Year graph line and the Previous Year graph line through DAX logic while displaying all weeks in the visual itself for the particular fiscal month that the Data Effective Date relates to. For instance, if the Data Effective Date is 5/19/22(which falls within week 4 of May 2022 in the fiscal calendar) and the fiscal calendar contains a total of 5 weeks for May 2022, then I want the graph visual to show all 5 weeks of the month where the Current Year line shows the gross profit for each week up to week 4(the line won't extend to week 5 because of the Data Effective Date of 5/19/22 as seen in the graph below). Additionally, I then want the Previous Year line to show the gross profit for each week up to week 5 as the line represents the gross profit for May 2021 and all 5 weeks within that month.

 

How would I achieve this behavior when no date slicers are selected? Also, using my "May 2022" scenario I described above can I get the same visual behavior when "2022" is selected from the Year slicer and "May" from the Month slicer? Based on what I described here, can I even achieve this behavior without date slicers being selected to filter the visual itself? I feel like the DAX logic itself isn't enough to achieve the behavior that I'm going for and some sort of filter on the visual itself is needed either through a slicer or a filter on the visual itself through the Filters pane.

 

Please let me know if you need more details about what I'm trying to achieve. The data model relationship is a standard 1-to-M relationship between the Dim Calendar(fiscal) and the Fct Transaction table.

 

CY and PY by Week of Month - Intended Behavior.png

1 ACCEPTED SOLUTION

Hi

 

Looking at the data you provided the error in your calculations is the context of the max date. I have made some debuggin to your measure and in  the last line, that is week 5 for the month, we get the 1013 (January month instead of the 1017) so the filter context for this specific line is incorrect since this should return the same value for all the lines the same happens on the PY values.

 

So in this case what you need to do is to change the filter context of your Max Month so if you replace the values that you have you should have the expected result:

Previous

VAR vMaxMonthID =[v_MaxMonthID] 

Updated
VAR vMaxMonthID =CALCULATE([v_MaxMonthID],ALLSELECTED('Dim Calendar'))

Has you can see on the bottom you get the expected result:

 

MFelix_7-1658391774951.png

MFelix_6-1658391764361.png

MFelix_5-1658391751196.png

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

9 REPLIES 9
parry2k
Super User
Super User

@Magellan2022 Thanks for reaching out, and other great community members are helping you out. Based on your initial post, it is doable without using any slicer etc. I would recommend creating a sample pbix file with the expected output and sharing it using one/google drive, it will help to provide the solution.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Magellan2022
Helper I
Helper I

@SpartaBI , @tamerj1 , @Jihwan_Kim , @PaulDBrown , @parry2k , @MFelix , @AlexisOlson 

 

I'm reaching out to you specifically to see if any guidance could be provided from your perspective, please. Any help would be appreciated! Even a high-level explanation of the fundamentals of how Power BI works that would explain if what I'm trying to do is possible for not would be welcomed as well....Thank you!!

Hi @Magellan2022 ,

 

Not really sure if I understood your setup and model configuration, but believe my main confusion comes from the fact that you refer the effective date and i get some what lost.

 

I can assume that you have the fiscal columns for year / month / week in year / week in fiscal month, if that is the case making your slicer based on the fiscxal year and month and the week chart based on the week in fiscal month should give you the needed calculation.

 

Has I told before I got lost on the effective date part, but the main concepts should work has described above, of course the calculations must be done using the fiscal columns.

 

Does it make sense? Can you please elaborate a little bit more, if you want you can send a privvate message so we arranje some time to discuss.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix 

 

Thanks for your response!

 

The [Data Effective Date].....measure in model -> MAX('Dim Organization'[_MAX_ROW_EFFECTIVE_DATE).....is based on when the data was last loaded into the data model for a particular Organization. The Power BI report is embedded into a website and when a user logs into the report, only the report data that is related to the Organization that the user belongs to is displayed. This filtering is done through row-level security based on the user principal name of the user viewing the report. 

 

When no fiscal date slicer values are selected in the report, then I want the [Data Effective Date] measure to drive the DAX calculations for Current Year and Previous Year lines in the line graph visual. You're correct that when fiscal date slicer values are selected, then I do get the correct intended behavior. I'm trying to get the same intended behavior when no fiscal date slicer values are selected where the [Data Effective Date] drives the DAX logic to show accurate numbers in the line graph visual.

 

If my explanation is not enough, then I will message you privately for further discussion.

 

 

Hi @Magellan2022 ,

 

Believe now I understand what you need, in this case you need to force your measures to pick up the fiscal data of the effective date.

 

You should try something similar to this:

 

VAR EffectDate =
IF(ISFILTERED('Calendar'[Date]), MAX ( 'Calendar'[Date] ), MAX(Table[EffectiveDate]) )
VAR MaxFiscalYearMonth =
    LOOKUPVALUE (
        'Calendar'[FiscalYearMonth],
        'Calendar'[Date], EffectDate
    )
RETURN
    CALCULATE (
        [Measure], --measure to be calculated
        'Calendar'[FiscalYearMonth] = MaxFiscalYearMonth,
        ALL ( 'Calendar' )
    )

 

 

Be aware that this may need some changes and adaptation, since I have made a change of a model I use, but basically you need to pick the date value if the calendar is filtered otherwise you will pick up the effective date then based on that you will pick up the correspondent fiscal date and use it to return the result you need for your measure.

 

Once again this depends on your model setup.

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix ,

 

I think we're on the same page or very close. I didn't use your DAX logic you posted here but have my own DAX that I put together already that almost gets me to the intended behavior with the line graph visual. The below will describe the issues I'm seeing...

 

-- Start of Scenario 1 --

-- This scenario is to demonstrate what happens when NO fiscal date slicer values are selected and only the [Data Effective Date] measure drives the DAX logic.

 

Referenced in the graph measures(CY and PY) as a variable.

v_MaxMonthID = 
VAR MaxWeek = [v_MaxWeekID]
RETURN
MAXX(FILTER('Dim Calendar','Dim Calendar'[Week ID] = MaxWeek),'Dim Calendar'[Month ID])

 

Below DAX is referenced in the v_MaxMonthID measure above. The Data Effective Date value for everything I'm explaining here in this post is 5/19/22(this falls within Week of Month 4) which is what ultimately drives all the DAX logic that you see.

v_MaxWeekID = 
VAR MaxData = [Data Effective Date]
RETURN
MAXX(FILTER('Dim Calendar','Dim Calendar'[Date] <= MaxData),'Dim Calendar'[Week ID])

 

CY measure

Graph - Gross Profit (CY) = 
VAR vMaxMonthID = [v_MaxMonthID]
RETURN 
    CALCULATE (
        [Transaction Amt GP*],
        FILTER (  'Dim Calendar'  , 'Dim Calendar'[Month ID] = vMaxMonthID )
    )

 

PY measure

Graph - Gross Profit (PY) = 
VAR vMaxMonthID1 = [v_MaxMonthID]
VAR vDataEffYear =
    CALCULATE (
        MAX ( 'Dim Calendar'[Year] ),
        FILTER ( ALL ( 'Dim Calendar' ), 'Dim Calendar'[Date] <= [Data Effective Date] )
    )
VAR vNumofWeeksPY =
    MAXX (
        FILTER ( ALL ( 'Dim Calendar' ), 'Dim Calendar'[Year] = vDataEffYear - 1 ), --this will be part of the the final return statement as 52 or 53 weeks depending on the fiscal calendar
        'Dim Calendar'[Week of Year]
    )
VAR vLastWeekofMaxMonth =
    CALCULATE (
        MAX ( 'Dim Calendar'[Week ID] ),
        FILTER ( 'Dim Calendar', 'Dim Calendar'[Month ID] <= vMaxMonthID1 )
    )
RETURN
    CALCULATE (
        [Transaction Amt GP*],
        FILTER (
            ALL ( 'Dim Calendar' ),
            'Dim Calendar'[Week ID] = vLastWeekofMaxMonth - vNumofWeeksPY  --this will either be 52 or 53 weeks depending on the fiscal calendar for given organization
        )
    )

 

Now, when the Fiscal Month and Fiscal Year date slicer values are NOT selected, then I get correct results for each week except for Week of Month 5. The added table visuals show that everything looks ok with the numbers for each Week of Month except for week 5.

 

Table Visual that's been converted from a Line Graph for easier readability.

GP - Incorrect week 5 values for CY and PY - Org 1.png

- For week 5, the value for the CY measure is actually from week 5 of January 2022 instead of week 5 of May 2022. (Expected value should be blank.)

- For week 5, the value for the PY measure is actually from week 5 of January 2021 instead of week 5 of May 2022. (Expected value should be 98,734.25)

 

Tables that tell me what the expected values should be for week 5 for May 2022 and May 2021.

GP - Expected Week of Month Values for May 2022 and May 2021 - Org 1.png

 

-- End of Scenario 1 --

 

-- Start of Scenario 2 --

-- This is to demonstrate what happens when fiscal Year and fiscal Month date slicer values are selected.

 

Table Visual that's been converted from a Line Graph for easier readability.

GP - Incorrect week 5 value ONLY for PY when slicers selected - Org 1.png

- For week 5, the value for the PY measure should not be blank. (Expected value should be 98,734.25)

- Everything else is correct.

 

-- End of Scenario 2 --

 

As you can tell, I'm trying to account for two different scenarios with the visual...one for when fiscal date slicer values ARE NOT selected vs when fiscal date slicer values ARE selected. This is where perhaps fundamentally this isn't possible based on filter context from the visual itself and the way Power BI is intended to work....but I could be wrong here.

 

Also, I mis-spoke earlier about everything looking correct when the fiscal date slicer values ARE selected. I have different, separate DAX measures that correctly show the line graph values ONLY when the fiscal date slicer values ARE selected but in reality I'm trying to solve the two scenarios I described above.

 

Hopefully, these details help with explaining what I'm trying to do.

 

 

Hi

 

Looking at the data you provided the error in your calculations is the context of the max date. I have made some debuggin to your measure and in  the last line, that is week 5 for the month, we get the 1013 (January month instead of the 1017) so the filter context for this specific line is incorrect since this should return the same value for all the lines the same happens on the PY values.

 

So in this case what you need to do is to change the filter context of your Max Month so if you replace the values that you have you should have the expected result:

Previous

VAR vMaxMonthID =[v_MaxMonthID] 

Updated
VAR vMaxMonthID =CALCULATE([v_MaxMonthID],ALLSELECTED('Dim Calendar'))

Has you can see on the bottom you get the expected result:

 

MFelix_7-1658391774951.png

MFelix_6-1658391764361.png

MFelix_5-1658391751196.png

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



amitchandak
Super User
Super User

@Magellan2022 , to year week this year vs last year

 

This week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last year same week  = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))

 

Also refer

Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

@amitchandak 

I tried your solution but it does not work for my specific scenario I described when no date slicer values are selected. I'm trying to see if I can create the behavior with the visual I provided in the original post that is driven by a Data Effective Date(e.g. 5/19/2022) field. I'm trying to figure out the two y-axis DAX measures (Curr Year and Prev Year) that would solve this but it seems some sort of filter is needed on the visual itself(from a slicer or filter from the filter pane) to achieve the final result I'm looking for. I believe there are some fundamentals of how Power BI is intended to work going on here that might not allow me to do what I want...this is why I'm reaching out to the Power BI Community to gain some clarity on whether or not what I'm trying to do is possible. At the moment, I'm not sure if I can achieve my scenario with ONLY using DAX logic for the Curr Year and Prev Year y-axis measures. I believe the filter context from the visual itself plays a role as well which leads me to think that date slicers or a filter pane filter on the visual itself are needed in addition to the DAX logic in the two y-axis measures.

 

NOTE: The DAX logic you did provide does work when Year, Month and Week of Month are added to the visual but my specific requirement is to ONLY show Week of Month on the x-axis.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.