Filtering several fields from the same column into a table using DAX

[Anonymous]

Hi, i am trying to retrieve 2 different fields within the same column from my data table to populate onto my PowerBI dashboard. For example, my data table contains lots of data, but i will only want to specifically extract data relating to the months of January and February under the dates column of my table.

I will like to know if there is a DAX formula that is able to do that? 

[dax]

Hi @Anonymous , 

You said that you want to get the data between date, did you want to show then in the same rows or just show rows which between date slicer? If you want to get rows which between date slicer, you could create a calendar table and create relationship between calendar table and your table, use calendar table as slicer, then it  should work. If this is not what you want, please correct and inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[Anonymous]

Hi, let me show an example which hopefully better illustrates my question.

I currently have a date slicer on my PowerBI dashboard in the same format as the dates column in the table shown above. 

The outcome i want to achieve is to create a measure which derives the cumulative revenue for a specific type of revenue when the dates slicer is selected. (I.e. Selecting Feb 20 on the dates slicer filters "Service" type revenues and "Jan-20" and "Feb-20" of revenue data)

[dax]

Hi @Anonymous , 

You could create a calculated column like below in your table mdate = "01-"&T3[Date], then change its type to date, and create slicer table like below, try to use below measure to see whether it work or not.

Measure = CALCULATE(SUM(T3[Revenue]), FILTER(T3,T3[Type]="Service" && T3[mdate]<=MIN('slicer'[month]) && DATEADD(T3[mdate],1,MONTH)>=MIN(slicer[month])))

Best Regards,
Zoe Zhi

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

[Anonymous]

Hi Zoe, thanks for your responses! 

Looking at your table and measure, is there a way to tweak the measure in a way which gives me the revenue values for cumulative months starting January as the date on the slicer changes? I.e. selecting the month of March gives me revenue from January to March

[dax · Accepted solution]

Hi @Anonymous ,

If you want to get date from Jan-March, I think you could try below measure based on my sample.

Measure 2 = CALCULATE(SUM(T3[Revenue]), FILTER(T3,T3[Type]="Service" && T3[mdate]<=MIN('slicer'[month])))

 If this doesn't work, could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[amitchandak]

@thamzh , Not very clear

There are many ways. You can have one month in data segmentation and filtering. You can have the formula

calculate(sum(table[Column]),format(table[date],"MMM-YYYY") en "Jan-2020","Feb-2020"-)

O
calculate(sum(table[Column]),filter(table, format(table[date],"MMM-YYYY") ? "Jan-2020" ? format(table[date],"MMM-YYYY") ?" Feb-2020"-))

[amitchandak]

@Anonymous , Not very clear

There many ways. You can have month year in slicer and filter. You can have the formula

calculate(sum(table[Column]),format(table[date],"MMM-YYYY") in {"Jan-2020","Feb-2020"})

Or
calculate(sum(table[Column]),filter(table, format(table[date],"MMM-YYYY") = "Jan-2020" || format(table[date],"MMM-YYYY") ="Feb-2020"}))