cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dataSeeker Frequent Visitor
Frequent Visitor

Showing 12 months of data

I have a Number (Gallons) that i want to divide by 12 and then show the annualized number in a visual by month. The starting month will change.   Is there a way to do this ?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Showing 12 months of data

Hi @dataSeeker,

 

In source table, create a calculated column.

Annualized Gallons = Data_2[Gallons]/12

1.PNG

 

Then, new a calculated table.

Result table =
VAR datedim =
    FILTER (
        CALENDAR ( DATE ( 2019, 4, 1 ), DATE ( 2020, 6, 1 ) ),
        DAY ( [Date] ) = 1
    )
VAR crossjoitablen =
    FILTER (
        CROSSJOIN ( Data_2, datedim ),
        [Date] >= Data_2[Target]
            && YEAR ( [Date] ) = YEAR ( Data_2[Target] )
    )
RETURN
    GROUPBY (
        crossjoitablen,
        [Date],
        "Gallon Potential", SUMX ( CURRENTGROUP (), [Annualized Gallons] )
    )

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User
Super User

Re: Showing 12 months of data

@dataSeeker 

 

probably yes, but we need more info than that Smiley Happy. Maybe a data sample along with expected results?

 


 


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


Proud to be a Datanaut!  

dataSeeker Frequent Visitor
Frequent Visitor

Re: Showing 12 months of data

Something like this

 

Data Sample

Gallons  Target  Annualized Gallons

12000      4/1/19     1000

24000       5/1/19     2000

6000         8/1/19      500

 

Visual:

Month  Gallon Potential

 April         1000

May           3000

June            3000

July             3000

August        3500

...

April 2020     3500

May               2500

June                 500

 

Community Support Team
Community Support Team

Re: Showing 12 months of data

Hi @dataSeeker,

 

In source table, create a calculated column.

Annualized Gallons = Data_2[Gallons]/12

1.PNG

 

Then, new a calculated table.

Result table =
VAR datedim =
    FILTER (
        CALENDAR ( DATE ( 2019, 4, 1 ), DATE ( 2020, 6, 1 ) ),
        DAY ( [Date] ) = 1
    )
VAR crossjoitablen =
    FILTER (
        CROSSJOIN ( Data_2, datedim ),
        [Date] >= Data_2[Target]
            && YEAR ( [Date] ) = YEAR ( Data_2[Target] )
    )
RETURN
    GROUPBY (
        crossjoitablen,
        [Date],
        "Gallon Potential", SUMX ( CURRENTGROUP (), [Annualized Gallons] )
    )

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.