Calculation Groups

[HomeEd]

I have daily data that needs to be shown daily, weekly (7 day rolling for 4 separate weeks), prior mo, MTD, YTD, and prior yr same week.  I have measures for each individual grouping but need to group these together so one slicer will work with all visuals, so users can select a day and it automatically adjusts for each of the groupings.  I am unable to download Tabular Editor.  How can I accomplish this without Tabular Editor?

[123abc]

Creating calculation groups in Power BI is a powerful way to group measures and apply them using a single slicer. However, as of my last knowledge update in September 2021, Power BI Desktop doesn't natively support the creation of calculation groups through its user interface. To create calculation groups without using Tabular Editor, you'll need to manually write the DAX expressions and create calculation tables.

Here's a simplified example of how you can create calculation groups for daily, weekly, prior month, MTD, YTD, and prior-year same week using DAX within Power BI Desktop:

1. Create a Date Table: Ensure you have a date table in your Power BI model, which includes daily dates.

2. Create Calculation Tables: For each grouping (daily, weekly, etc.), create a separate calculated table with the appropriate logic. You can use DAX functions like SUMMARIZE and FILTER to create these tables. For example, here's how you can create a weekly table:

Weekly = SUMMARIZE('Date', 'Date'[Week], "StartDate", MIN('Date'[Date]), "EndDate", MAX('Date'[Date]))

1. This code creates a table that summarizes data by week, capturing the start and end dates of each week.

2. Create Measures: Create measures for each grouping (daily, weekly, etc.). For example, create a measure for daily sales, weekly sales, etc., using the calculated tables created earlier. Here's how you can create a weekly sales measure:

Weekly Sales = CALCULATE( SUM('Sales'[Amount]), FILTER(ALL('Weekly'), 'Date'[Date] >= 'Weekly'[StartDate] && 'Date'[Date] <= 'Weekly'[EndDate]) )

1. This measure calculates the sum of sales for the selected week using the calculated "Weekly" table.

2. Create Calculation Group: Create a calculated table to serve as your calculation group. This table will hold the values for your slicer. For example:

CalculationGroup =

UNION( ROW("Grouping", "Daily"),

ROW("Grouping", "Weekly"),

ROW("Grouping", "Prior Month"),

ROW("Grouping", "MTD"),

ROW("Grouping", "YTD"),

ROW("Grouping", "Prior Year Same Week") )

1. Create a Slicer Visual: Add a slicer visual to your report and use the "Grouping" column from your "CalculationGroup" table as the slicer field. This slicer will allow users to select the desired grouping.

2. Create Visuals: Create visuals (tables, charts, etc.) in your report and use the appropriate measures based on the selected grouping from the slicer.

This approach requires manual creation of calculated tables and measures for each grouping, and it can become complex as you add more groupings. Tabular Editor simplifies the process by allowing you to create calculation groups more efficiently.

Please note that Power BI features and capabilities may have evolved since my last update in September 2021. You may want to check the latest Power BI documentation or community forums for any new features related to calculation groups.

[some_bih]

Hi @HomeEd 

alternative 1: check link below and create manually all measures what you need

Standard time-related calculations – DAX Patterns

alternative 2: if possible install Bravo, part Time intelligence for your measure (this tool will automatically do everything for alternative 1 above)

https://www.sqlbi.com/tools/bravo-for-power-bi/ 

Hope this help, kudos appreciated

[HomeEd]

The user needs to select a date, for instance, today,yesterday, and etc. and that one selection needs to change all visuals for daily, weekly (7 day rolling for 4 separate weeks), prior mo, MTD, YTD, and prior yr same week.  I can't download a third-party tool so how do I make alternative 1 with the separate measures work with one slicer based on a day selection since by data is by day?

[some_bih]

Hi @HomeEd try possible solution from user 123abc