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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
yohanhwang
Frequent Visitor

how to group by using if or switch statements in calculated table using DAX

Hello there,

 

Pretty new to Power BI and could use some help. So I understand how to generate a table grouping by using SUMMARIZE using some easy "default" options. But what if I wanted to group by very specific criteria, such as a date range, so specific that it needs to be spelled out using DATESBETWEEN. 

 

For some context, here is the "ungrouped" cleaned date I'm trying to work with. I cut out the names of people:

Original "Ungrouped" dataOriginal "Ungrouped" data

 

Here I successfully grouped by very simply by year using SUMMARIZE:

Successfully grouped just by yearSuccessfully grouped just by year

 

But on the pic below I was unsuccessful in trying to group by very specific date ranges. I can't seem to figure out the DAX for this. Any help would be greatl appreciated.

unsuccessfully trying to group it by specific date rangesunsuccessfully trying to group it by specific date ranges

9 REPLIES 9
jewel_at
Helper II
Helper II

If you have a calendar table you should be able to assign the Year for the date range (I assume this is your Fiscal year), add the relationship with your [Contribution Date] in your data model and the calendar's date and then use the [Fiscal Year] in your SUMMARIZE 

 

There are a couple of ways to create a Calendar table, this a good article with sample files that you can download

Power BI Date or Calendar Table Best Method: DAX or Power Query? - RADACAD

 

Hope this helps! Let me know 

 

Jewel

Thank you for your prompt reply "jewel_at". Actually I'm not exactly using the Fiscal Year. In this example I was just playing around with only a sample of data, and my playing around does make it look like I want the Fiscal Year. But that's not really the case. What I really need for each year for the past five years, is to go from start of fiscal year (7/1 in our case) up to today in that year. So for instance Fiscal year 2019 would be from 7/1/19 to 1/5/20. And that would be for every year. (I'm writing this on the fifth of January). I think I can figure out that part of the code by using TODAY or something like that, but the hard part is to SUMMARIZE by SWITCH or IF statements. Could you help?

 

I see! You need the sum of values in between varying date period? 

 

For example, 

jewel_at_0-1672960959004.png

 

Based on what you wrote e.g. 2019 is sum of contribution in between 7/1/2019 until Today, your expected result is this:

jewel_at_1-1672961106827.png

Correct?

If yes, then this is the DAX

 

Measure = 

var MaxDate = TODAY()
var MaxYear = MAX('Calendar'[Year])
RETURN
SWITCH(MaxYear,
2019, 
   CALCULATE(AVERAGEX(VALUES('Calendar'[Date]), SUM('Table'[Contribution])),
   DATESBETWEEN('Calendar'[Date], DATEVALUE("7/1/2019"),MaxDate))
,
2020,
    CALCULATE(AVERAGEX(VALUES('Calendar'[Date]), SUM('Table'[Contribution])),
   DATESBETWEEN('Calendar'[Date], DATEVALUE("7/1/2020"),MaxDate)
)
,
2021,
    CALCULATE(AVERAGEX(VALUES('Calendar'[Date]), SUM('Table'[Contribution])),
   DATESBETWEEN('Calendar'[Date], DATEVALUE("7/1/2021"),MaxDate)
),

2022,
    CALCULATE(AVERAGEX(VALUES('Calendar'[Date]), SUM('Table'[Contribution])),
   DATESBETWEEN('Calendar'[Date], DATEVALUE("7/1/2022"),MaxDate)
)

)

 

 

This would still require that you have a Calendar table (everyone should have one anyway 🙂)

 

The only thing this is you would have to manually update this every year, I'm sure there's another way to write this where it would be dynamic. 

 

You can download the test I made here:

Calendar Sum DatesInBetween 

 

Hope this helps you be on track with your report!

 

Let me know 

 

Jewel

 

Hi,

 

So I pretty much tried copy and pasting your code first as a new table (said it's not correct for a table) and then as a new column in the table. I obviously changed the pertinent values to correspond existing tables and columns. (I did create a calendar table). But the result that I get is an empty table as shown in the bottom of the pic. 

yohanhwang_0-1673027536695.png

 

Hi @yohanhwang 

 

Did you connect your Calendar table with Contribution table? 

 

You would need to create a Measure instead of a new  Column. You also need your Calendar table to have a Year column.

 

To test use a table visual to see the how the Primary Amount aggregate based on the condition in the measure.

 

 

Jewel

 

 

 

Ok, so I tried that, doing it like this:

yohanhwang_0-1673386997369.png

 

But this is the result that I get, which doesn't make sense. Why is it adding everything and is the same for all years? :

yohanhwang_1-1673387086115.png

 

 

Hi @yohanhwang 

 

Try to create the new Measure in your Contribution table, and in your data model, create a relationship with your Calendar and Contribution table. 

 

Hope that works!

 

Jewel

 

 

Hi Jewel_at,

 

I just received your private message. I did put the screenshots here. If I'm missing a screenshot what precisely would you need on it?

Hi @yohanhwang 

 

Could you please add a screenshot of your data model?

 

I noticed that you created a new table (SwitchTable) and added the new measure there. 

If there's no relationship or connection between the SwitchTable and your Calendar, you won't be able to aggregate by Year.

 

 

Jewel

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors