Calculate sum of column based on selected Month from slicer

[jay_patel]

I have a table which contains data as shown below: 

Above shown data is just a sample data , in actual data the columns are up to TransactionCurrency12 and fiscal year from 2017 to current fiscal year.

I also have a month-year slicer which has values like Apr-2023, May-2022, Jun-2018 and so on. 

Now, I want to display sum in such a manner that if I am selecting Apr-2023 from slicer then, it should display sum of TransactionCurrency01 where fiscal year = 2023 , if May-2022 then sum of TransactionCurrency01 + TransactionCurrency02 where fiscal year = 2022, if Jun-2023 then sum of TransactionCurrency01 + TransactionCurrency02 + TransactionCurrency03 where fiscal year = 2023, if Jul-2021 , then sum of TransactionCurrency01 + TransactionCurrency02 + TransactionCurrency03 + TransactionCurrency04 where fiscal year = 2021 and so on .

Currently , I am using below DAX   by writing condition for all month-year which is not feasible:

VAR SelectedMonth = SELECTEDVALUE(Calendar_New2[month year new])

RETURN

SWITCH(

    SelectedMonth,

    "Apr-2023", SUM('copy of GLT0'[TransactionCurrency01]),

    "May-2023", SUM('copy of GLT0'[TransactionCurrency01]) + SUM('copy of GLT0'[TransactionCurrency02]),

    "Jun-2023", SUM('copy of GLT0'[TransactionCurrency01]) + SUM('copy of GLT0'[TransactionCurrency02]) +SUM('copy of GLT0'[TransactionCurrency03]) .... and so on , 

@amitchandak 

[Greg_Deckler]

@jay_patel So typically you would want to unpivot those columns and that will make your life much easier. Otherwise, you need something like MC Aggregations: https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggreg...

In your case, if you unpivot your columns and create a calculated column of RIGHT([Attribute],1)+0 then you would have a numeric value that you could use <= in your calculation as a filter.

If this doesn't help, post your data as text and what you want to end up with.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[jay_patel]

@Greg_Deckler 

Below is my sample data ,

Wholesalers	Fiscal_Year	TransactionCurrency01	TransactionCurrency02	TransactionCurrency03
A	2023	0	0	0
B	2023	758.45	2129.56	2385.11
C	2023	0	0	0
D	2023	780089.39	4607.26	18454.92
E	2022	-6006	0	-2933.2
F	2022	-230454.84	0	-8504
G	2022	-10549.14	0	-1230.93

I also have a month year slicer which has values from Jan-2018 to current month year i.e. Apr-2024

Now, I want to display sum in such a manner that if I am selecting Apr-2023 from slicer then, it should display sum of TransactionCurrency01 where fiscal year = 2023 ,

if May-2022 then sum of TransactionCurrency01 + TransactionCurrency02 where fiscal year = 2022,

if Jun-2023 then sum of TransactionCurrency01 + TransactionCurrency02 + TransactionCurrency03 where fiscal year = 2023,

if Jul-2021 , then sum of TransactionCurrency01 + TransactionCurrency02 + TransactionCurrency03 + TransactionCurrency04 where fiscal year = 2021 and so on .

Expected output is as shown below :

Wholesalers	Value 1 (Jun-2023)
A	0
B	5273.12
C	0
D	803151.57
E	0
F	0
G	0

In the output below , we are assuming that Jun-2023 is selected from month year slicer , so the values shown in column Value 1 (Jun-2023) is summation of TransactionCurrency01 + TransactionCurrency02 + TransactionCurrency03 where fiscal year = 2023 against respective wholesaler . For example : For wholesaler B , 758.45 + 2129.56 + 2385.11 = 5273.12 and so on .

@amitchandak 

[Greg_Deckler]

@jay_patel Once you unpivot your data and add a few helper columns this becomes pretty simple. See attached PBIX below signature.

Measure = 
    VAR __Wholesaler = MAX('Table'[Wholesalers  ])
    VAR __FiscalYear = MAX('Table'[Fiscal_Year  ])
    VAR __Date = MAX('Table'[Date])
    VAR __Table = FILTER(ALL('Table'), [Wholesalers  ] = __Wholesaler && [Fiscal_Year  ] = __FiscalYear && [Date] <= __Date)
    VAR __Result = SUMX( __Table, [Value] )
RETURN
    __Result

[jay_patel]

@Greg_Deckler 

Your DAX works fine with sample data . But when TransactionCurrency10, TransactionCurrency11 and TransactionCurrency12 comes into the picture then it creates some sort of issue.

Below is the table which tells that what should be the summation of TransactionCurrency with respected month year : 

(I have total 12 column of TransactionCurrency in data)

Month	TransactionCurreny
Apr	TransctionCurrency01
May	TransctionCurrency01+TransctionCurrency02
Jun	TransctionCurrency01+TransctionCurrency02+TransctionCurrency03
Jul	TransctionCurrency01+TransctionCurrency02+TransctionCurrency03+TransctionCurrency04
Aug	TransctionCurrency01 to TransctionCurrency05
Sep	TransctionCurrency01 to TransctionCurrency06
Oct	TransctionCurrency01 to TransctionCurrency07
Nov	TransctionCurrency01 to TransctionCurrency08
Dec	TransctionCurrency01 to TransctionCurrency09
Jan	TransctionCurrency01 to TransctionCurrency10
Feb	TransctionCurrency01 to TransctionCurrency11
Mar	TransctionCurrency01 to TransctionCurrency12

Currently, when I am selecting Jul-2023 from slicer then the <= conditon from DAX includes data from Jan-2023 to Jul-2023 (refer below table after unpivoting and creating few calculated columns as given in PBIX file above) , but it should give data from Apr-2023 to Jul-2023 i.e from  TransctionCurrency01+TransctionCurrency02+TransctionCurrency03+TransctionCurrency04 (same for all fiscal years) :

Attribute	Custom	addition	year mon	date
TransctionCurrency10	10	1	202301	01-Jan-23
TransctionCurrency10	10	1	202301	01-Jan-23
TransctionCurrency11	11	2	202302	01-Feb-23
TransctionCurrency11	11	2	202302	01-Feb-23
TransctionCurrency12	12	3	202303	01-Mar-23
TransctionCurrency12	12	3	202303	01-Mar-23
TransctionCurrency01	1	4	202304	01-Apr-23
TransctionCurrency01	1	4	202304	01-Apr-23
TransctionCurrency02	2	5	202305	01-May-23
TransctionCurrency02	2	5	202305	01-May-23
TransctionCurrency03	3	6	202306	01-Jun-23
TransctionCurrency03	3	6	202306	01-Jun-23
TransctionCurrency04	4	7	202307	01-Jul-23
TransctionCurrency04	4	7	202307	01-Jul-23

Also, please do check for months Jan , Feb , Mar also as I think the above DAX won't work there. i.e if I am selecting Jan then it should sum from TransctionCurrency01 to TransctionCurrency10 (as mentioned in 1st table). 

@amitchandak 

[Greg_Deckler · Accepted solution]

@jay_patel This is a bit hacky but works. See PBIX. You really should be using a fiscal calendar for this sort of thing.